Error In Dynamic Sql Sp

I Have created a procedure which has code like this: Create PROCEDURE Sample( @ID INT ) AS BEGIN DECLARE @SQL NVARCHAR(max) DECLARE @SchemaName SYSNAME DECLARE

Solution 1:

Your string literal has an unfortunate length. Implicit string conversion from varchar to nvarchar truncates strings with a length between 4000 and 8000 characters to 4000 characters.

Use the N prefix before your string literal to avoid implicit string conversion.

Solution 2:

Don't use PRINT. Chances are that the PRINT output itself will be truncated with long text. Use

SELECT @SQLas [processing-instruction(x)] FOR XML PATH 

to inspect the values of such variables in SSMS.

Solution 3:

Sometimes when using dynamic sql, I find it helpful to have it print the variable:

PRINT @SQL

That way you can take the output and look at it in the analyser

Solution 4:

I changed your initial declaration of @SQL to DECLARE @SQL VARCHAR(max) and it worked (instead of NVARCHAR).