SQL 如何使用打印语句打印 VARCHAR(MAX)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7850477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to print VARCHAR(MAX) using Print Statement?
提问by peter
I have a code which is:
我有一个代码是：
DECLARE @Script VARCHAR(MAX)
SELECT @Script = definition FROM manged.sys.all_sql_modules sq
where sq.object_id = (SELECT object_id from managed.sys.objects
Where type = 'P' and Name = 'usp_gen_data')
Declare @Pos int
SELECT @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,7500)
PRINT SUBSTRING(@Script,1,@Pos)
PRINT SUBSTRING(@script,@pos,8000)
The length of the Script is around 10,000 Characters and Since I am using print Statement which can hold only max of 8000. So I am using two print statements.
脚本的长度约为 10,000 个字符，并且由于我使用的打印语句最多只能容纳 8000 个字符。所以我使用了两个打印语句。
The problem is when I have a script which is of say 18000 characters then I used to use 3 print statements.
问题是当我有一个 18000 个字符的脚本时，我曾经使用 3 个打印语句。
So Is there a way that I could set the number of print statements depending on the length of the script?
那么有没有办法可以根据脚本的长度设置打印语句的数量？
采纳答案by Kelsey
You could do a WHILEloop based on the count on your script length divided by 8000.
您可以WHILE根据脚本长度除以 8000 的计数进行循环。
EG:
例如：
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@script) / 8000) + 1
WHILE @Counter < @TotalPrints
BEGIN
-- Do your printing...
SET @Counter = @Counter + 1
END
回答by alfoks
I know it's an old question, but what I did is not mentioned here.
我知道这是一个老问题，但这里没有提到我所做的。
For me the following worked.
对我来说，以下工作有效。
DECLARE @info NVARCHAR(MAX)
--SET @info to something big
PRINT CAST(@info AS NTEXT)
回答by Jirka Hanika
The following workaround does not use the PRINTstatement. It works well in combination with the SQL Server Management Studio.
以下解决方法不使用该PRINT语句。它与 SQL Server Management Studio 结合使用效果很好。
SELECT CAST('<root><![CDATA[' + @MyLongString + ']]></root>' AS XML)
You can click on the returned XML to expand it in the built-in XML viewer.
您可以单击返回的 XML 以在内置 XML 查看器中展开它。
There is a pretty generous client side limit on the displayed size. Go to Tools/Options/Query Results/SQL Server/Results to Grid/XML datato adjust it if needed.
显示大小有一个非常慷慨的客户端限制。Tools/Options/Query Results/SQL Server/Results to Grid/XML data如果需要，请转到 进行调整。
回答by Ben B
Here is how this should be done:
这是应该如何完成的：
DECLARE @String NVARCHAR(MAX);
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset tinyint; /*tracks the amount of offset needed */
set @string = replace( replace(@string, char(13) + char(10), char(10)) , char(13), char(10))
WHILE LEN(@String) > 1
BEGIN
IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(char(10), @String) -1
set @offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
set @offset = 1
END
PRINT SUBSTRING(@String, 1, @CurrentEnd)
set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String))
END /*End While loop*/
回答by Edyn
Came across this question and wanted something more simple... Try the following:
遇到这个问题并想要更简单的东西......尝试以下操作：
SELECT [processing-instruction(x)]=@Script FOR XML PATH(''),TYPE
回答by Andrey Morozov
This proc correctly prints out VARCHAR(MAX)parameter considering wrapping:
VARCHAR(MAX)考虑到包装，此过程正确打印出参数：
CREATE PROCEDURE [dbo].[Print]
@sql varchar(max)
AS
BEGIN
declare
@n int,
@i int = 0,
@s int = 0, -- substring start posotion
@l int; -- substring length
set @n = ceiling(len(@sql) / 8000.0);
while @i < @n
begin
set @l = 8000 - charindex(char(13), reverse(substring(@sql, @s, 8000)));
print substring(@sql, @s, @l);
set @i = @i + 1;
set @s = @s + @l + 2; -- accumulation + CR/LF
end
return 0
END
回答by Matthew Radford
I was looking to use the print statement to debug some dynamic sql as I imagin most of you are using print for simliar reasons.
我想使用 print 语句来调试一些动态 sql，因为我想你们中的大多数人都出于类似的原因使用 print。
I tried a few of the solutions listed and found that Kelsey's solution works with minor tweeks (@sql is my @script) n.b. LENGTH isn't a valid function:
我尝试了列出的一些解决方案，发现 Kelsey 的解决方案适用于小 tweeks（@sql 是我的 @script）nb LENGTH 不是有效函数：
--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Kelsey
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@sql) / 4000) + 1
WHILE @Counter < @TotalPrints
BEGIN
PRINT SUBSTRING(@sql, @Counter * 4000, 4000)
SET @Counter = @Counter + 1
END
PRINT LEN(@sql)
This code does as commented add a new line into the output, but for debugging this isn't a problem for me.
这段代码按照注释在输出中添加了一个新行，但对于调试这对我来说不是问题。
Ben B's solution is perfect and is the most elegent, although for debugging is a lot of lines of code so I choose to use my slight modification of Kelsey's. It might be worth creating a system like stored procedure in msdb for Ben B's code which could be reused and called in one line?
Ben B 的解决方案是完美的，也是最优雅的，尽管调试需要很多行代码，所以我选择使用我对 Kelsey 的轻微修改。可能值得在 msdb 中为 Ben B 的代码创建一个类似存储过程的系统，该系统可以在一行中重复使用和调用？
Alfoks' code doesn't work unfortunately because that would have been easier.
不幸的是，Alfoks 的代码不起作用，因为那会更容易。
回答by Marwan Almukh
You can use this
你可以用这个
declare @i int = 1
while Exists(Select(Substring(@Script,@i,4000))) and (@i < LEN(@Script))
begin
print Substring(@Script,@i,4000)
set @i = @i+4000
end
回答by Adam Gering
create procedure dbo.PrintMax @text nvarchar(max)
as
begin
declare @i int, @newline nchar(2), @print varchar(max);
set @newline = nchar(13) + nchar(10);
select @i = charindex(@newline, @text);
while (@i > 0)
begin
select @print = substring(@text,0,@i);
while (len(@print) > 8000)
begin
print substring(@print,0,8000);
select @print = substring(@print,8000,len(@print));
end
print @print;
select @text = substring(@text,@i+2,len(@text));
select @i = charindex(@newline, @text);
end
print @text;
end
回答by Yovav
Or simply:
或者干脆：
PRINT SUBSTRING(@SQL_InsertQuery, 1, 8000)
PRINT SUBSTRING(@SQL_InsertQuery, 8001, 16000)
