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

Answer 1

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

Answer 2

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.