CODEWITHSUNDEEP
sql-server
ganesh
ganesh

Reputation: 317

SQL Server : stored procedure parameter truncated while using dynamic query

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

Upvotes: 5

Views: 3972

Answers (4)

ganesh
ganesh

Reputation: 317

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.

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 2

Serpiton
Serpiton

Reputation: 3684

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

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

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.

Upvotes: 3

Related Questions