SQL Server : stored procedure parameter truncated while using dynamic query

Question

I am using a dynamic stored procedure, the parameter value is really long (more than 8000 characters).

And another stored procedure is calling inside the dynamic query (@SP), when I execute the SP the parameter being truncated.

How can I get the entire parameter? Did I miss some thing??

Because of this I am unable to execute the query.

ALTER PROCEDURE [dbo].["SP_NAME"]
    @ID varchar(50),
    @<parameter> nvarchar(max), 
AS
   SET NOCOUNT ON

   DECLARE @SP nvarchar(MAX)
   set @SP = '

   DECLARE @sampleNVARCHAR nvarchar(MAX)
   SET @sampleNVARCHAR= '''+ @<parameter>+ '''

   EXEC <anotherSP> @sampleNVARCHAR,'+ cast(@CLIENTOFFSET as varchar(10)) +''

   EXEC sp_executesql @SP
   RETURN

Try printing the @<parameter> in the sp body. Is it printing all the 8000 characters? — Nisha
– Nisha, Commented May 9, 2014 at 10:20
anotherSP have two parameters only. @<parameter> getting from the main SP(SP_NAME) and its assigned to @sampleNVARCHAR, and next is datetime — ganesh
– ganesh, Commented May 9, 2014 at 10:45
Is the nested stored procedure also having nvarchar(max) param size? Also, are you really combing varchar and nvarchar? — Mike M
– Mike M, Commented May 9, 2014 at 10:50
@MikeM yeah the nested SP also having nvarchar(max).no. i just tried to combine nvarchar(max) to another nvarchar(max). — ganesh
– ganesh, Commented May 9, 2014 at 10:58

Mudassir Hasan · Accepted Answer · 2014-05-09 10:48:45Z

3

The 8000 character limit does not applies to NVARCHAR(MAX) , you can specify upto n=4000 characters if you are EXPLICITLY specifying NVarchar(N) and upto N=8000 characters if you are using VARCHAR(N).

NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes which comes out to more than 2 billion characters .

If error being thrown shows string truncated message , check inside inner stored procedure for truncation error.

answered May 9, 2014 at 10:48

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ganesh Over a year ago

Actually,i tried to PRINT @SP in this SP.here itself it was trimmed off.

Maurice Reeves Over a year ago

Print only supports up to 8000 characters. The rest of the data is probably there.

ganesh Over a year ago

yeah...but when i tried through the application,got the syntax error because of the parameter no completed

Damien_The_Unbeliever Over a year ago

That middle paragraph is nonsense. Where did you come up with that 65535 number? Also, even the 2GB limit doesn't apply to varchar(max) variables.

FranTastic Over a year ago

Muchas gracias. Thanks. It was driving me crazy, I didn't know why an NVARCHAR(MAX) input parameter was truncating the input to 65,535 characters. Now I know that the information was there, it was just the SELECT that represented it wrong.

Mikael Eriksson · Accepted Answer · 2014-05-09 12:57:59Z

2

If you lead of the concatenation with a nvarchar(max) variable it will work for you.

Set @SP to an empty string and the use @SP in the concatenation expression.

declare @SP nvarchar(max);
set @SP = '';
set @SP = @SP + N'declare @sample....' + @Param

A test you can run on your own machine or in this SQL Fiddle

declare @SP nvarchar(max)
declare @Param nvarchar(max) = 'ParamString'

set @SP = replicate(N'X', 3000) + replicate(N'Y', 3000) + @Param
select len(@SP) -- Truncated to 4011 characters

set @SP = ''
set @SP = @SP + replicate(N'X', 3000) + replicate(N'Y', 3000) + @Param
select len(@SP) -- Not truncated, shows 6011 characters

edited May 9, 2014 at 12:57

answered May 9, 2014 at 11:08

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

ganesh · Accepted Answer · 2015-10-20 05:48:33Z

2

This can be because of implicit conversion

Implicit conversions are not visible to us. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

answered Oct 20, 2015 at 5:48

ganesh

3175 silver badges13 bronze badges

Comments

Serpiton · Accepted Answer · 2014-05-09 12:34:10Z

1

Instead of inserting the paramater in the text of the dynamic query is possible to pass them as parameters too, @CLIENTOFFSET is not defined in the OP script, the value is invented

ALTER PROCEDURE [dbo].["SP_NAME"]
    @ID varchar(50),
    @<parameter> nvarchar(max), 
AS
   SET NOCOUNT ON

   DECLARE @SP nvarchar(MAX)
   set @SP = '

   DECLARE @sampleNVARCHAR nvarchar(MAX)
   SET @sampleNVARCHAR= @param

   EXEC <anotherSP> @sampleNVARCHAR, cast(@CLIENTOFFSET as varchar(10))'

   EXEC sp_executesql @SP, N'@param nvarchar(max), @CLIENTOFFSET int'
      , @param = @<parameter>, @CLIENTOFFSET = @ID
   RETURN

The see the full definition of sp_executesql its technet page is probably the best choice

answered May 9, 2014 at 12:34

Serpiton

3,6843 gold badges26 silver badges35 bronze badges

Collectives™ on Stack Overflow

SQL Server : stored procedure parameter truncated while using dynamic query

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related