0

I have a varchar(max) variable named as QUERY. I'm storing a bulk of create queries in it. Its length is almost 65000+ characters. But whenever I print it or select it, it does not returns me the whole result.

DECLARE @QUERY AS VARCHAR(MAX)
SET @QUERY='';
//repopulating @QUERY with queries until the lengths reaches more than 65000 
//characters, then

SELECT LEN(REPLACE(@QUERY, 'N', ''))--to check the length
PRINT @QUERY as QUERY --to get the result in print
SELECT @QUERY as QUERY --to get the result in select

How can I get my whole result? All this work is being done in a stored procedure. And the result of the procedure should be the bunch of queries from the select statement.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Hur Abbas
  • 115
  • 10
  • 4
    [PRINT](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/print-transact-sql?view=sql-server-ver15#remarks) returns 4,000 characters for a Unicode string and up to 8,000 characters for non-Unicode string. – Zhorov Nov 04 '19 at 07:20

1 Answers1

1

Try

PRINT CAST(@QUERY AS NTEXT)

ntext , text, and image data types has been removed in a latest version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.

You could do a WHILE loop based on the count on your script length divided by 8000.

EG:

DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@QUERY) / 8000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    -- Do your printing...
    SET @Counter = @Counter + 1
END

FIDDLE DEMO

enter image description here

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • 1
    Seem you are correct, I finally managed to reproduce it myself. What an unusual "feature" for SQL Server. – Dale K Nov 04 '19 at 08:31
  • Its working but, after getting the output, rejoining it and executing as a query gives errors. – Hur Abbas Nov 04 '19 at 09:43