0

1.i Declare it like this:

declare @sql6 nvarchar(max)=''
--RETURN
SET @defs = N'
        @groupCode          VARCHAR(128),
        @zScore             float=''.95''';

set @sql6=@sql6+'BUNCH OF CODE >8000' 
PRINT CAST(@SQL6 AS NTEXT)
        EXEC sp_executesql @SQL6, @defs, 
                @GroupCode               = @GroupCode,
                @zScore                  = @zScore

  1. Should the statement PRINT CAST(@SQL6 AS NTEXT) print an unlimited amount of characters?

  2. My code has a syntax error in the following:

the print statement prints:

SELECT
    P.SourceCode,
    P.TreatmentID,
    P.ProductID,
    P.OptimizationGroupID,
    P.TreatmentOrdinal,
    CASE WHEN P.PricingType = 'Treatment' THEN 'Treatment 0' ELSE P.PricingType END PricingType,
    COALESCE(QuantityLooks, 0) AS QuantityLooks, --n
    COALESCE(LooksPercentag

and the errror statement says:

Msg 102, Level 15, State 1, Line 350
Incorrect syntax near 'LooksPercentag'.

So it seems the dynamic sql is getting cut off there.

From my previous research I had thought my code could be basically unlimited in length (less than 2^31 bytes or something) if I did the set @sql6=@sql6+'BUNCH OF CODE >8000'

Am I missing something?

Adam Sanders
  • 125
  • 11
  • take a look at: http://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated – FLICKER Apr 25 '16 at 22:58
  • Ive been trying some different suggestions from there, but same problem keeps coming up. il keep going :) – Adam Sanders Apr 25 '16 at 23:15
  • try set @sql6=@sql6+N'BUNCH OF CODE >8000' and also use SELECT instead of PRINT. and see if it still is truncated or not – FLICKER Apr 25 '16 at 23:17
  • You're missing the `N` prefix on the string literal and you can't rely on SSMS to show you the whole result with `print` or `select` except if you are returning XML and have set the options for XML results in SSMS to be unlimited. – Martin Smith Apr 25 '16 at 23:30
  • @FLICKER when I do the select SQL6 it says incorrect syntax near SQL6. – Adam Sanders Apr 25 '16 at 23:36
  • this works declare &sql6 nvarchar(max)---- set &sql6 = &sql6 + N'BUNCH OF CODE >8000' ---- select &sql6--- exec &sql6--- replace & with At Sign – FLICKER Apr 25 '16 at 23:42
  • hmm, the results of the select statement also show the code getting cut off – Adam Sanders Apr 26 '16 at 01:38

0 Answers0