Error in Dynamic SQL SP

Question

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 @TableName SYSNAME
    DECLARE @DatabaseName SYSNAME


     SELECT  @SQL = 'Create Table ' + @DatabaseName + '.'
            + @SchemaName + '.' + @TableName
            + '_temp' + '('
    SELECT  @SQL = @SQL + 'ID int NOT NULL Primary Key, Name VarChar(10))'  

I Always get error as :

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

Can anyone help me on this?

Dagall

Answer 1

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

Padlock

I tried adding print to the Procedure when It prints it is printing only until [Q18_RAW] [varchar](max) NULL,. Any Suggestions on why it is doing – Dagall

The max for NVARCHAR is 4K characters. Perhaps your SQL has a bunch of spaces in it? Do a PRINT LEN(@SQL) and see if it's 4K – Padlock

This is not remotely true. nvarchar(max) allows a billion characters (2GB of data and 2 bytes per character) – Restore

@Martin - Sorry you're correct. The maximum numerical value you can pass in the declaring statement is 4K. I misread the documentation here: msdn.microsoft.com/en-us/library/ms186939.aspx – Padlock

Answer 2

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.

Rhyolite

Too long? I've never hit this issue myself. How long can a string literal be in TSQL? – Restore

@Martin - If you do direct assignment @SQL = 'long string' it works. But if you do @SQL = @SQL + 'long string' the literal can not have a datalength > 8000. I have never heard about this either I just discovered the behavior and have not found any documentation of the limit. It would be really nice if you could confirm that you see the same thing. – Rhyolite

@Martin - I can't reproduce this any longer. Change the faulty code to use varchar instead to see the difference and the change back has somehow fixed it. I will look in to this more later. Strange indeed. – Rhyolite

@Mikael - What was the datype of @SQL in your test? I just tried (SQL Server 2008) declare @sql nvarchar(max) = ''; set @sql = @sql + 'some very long text I pasted in'; select len(@sql) and it returned 3776000 (Adding an N prefix before the literal didn't change the result either...) – Restore

@Martin - I use nvarchar(max). I am now able to reproduce again using the string in version one of this question. The behavior I see now is the same as I described in my first comment. Adding N prefix makes it work. – Rhyolite

Yes I see what you mean. It cuts off after [Q19_RAW] [varchar](max) NULL, – Restore

It seems to me that if it is >8000 characters then it is OK. But if it is between 4000 and 8000 characters it gets truncated. – Restore

@Martin It cuts every string that has a datalength() from 8000 to 16000. A string with 16001 characters works fine. – Rhyolite

@Martin - :) Pretty much the same as I discovered. – Rhyolite

@Mikael - I guess this must be a bug. Not something I've ever seen documented and certainly weird behaviour. – Restore

@Martin - Yes, most likely a bug. I tested in SQL Server 2005 and got the same thing from 4000 to 8000 chars. Above 8000 I got The data types nvarchar(max) and text are incompatible in the add operator. Since the string is not nvarchar because of the lacking N I guess that it is treated as a varchar with a specific length until it passes 8000 chars. The string then gets truncated by the implicit string conversion from varchar to nvarchar. – Rhyolite

It's not restricted to string literals. If I try DECLARE @sql NVARCHAR(MAX) = ''; declare @t table(c varchar(8000)) insert into @t values (replicate('A',8000)) select LEN(@sql + c) from @t I see the same results. In the compute scalar in the plan varchar(8000) gets implicitly converted to nvarchar(4000) maybe this is documented somewhere... – Restore

@Martin - I think I should update my answer :). BTW you can simplify the fail concatenation to this LEN(N''+c) – Rhyolite

@Mikael - Agreed but that wouldn't be as surprising to me. I think I was expecting the cast to be nvarchar(max) as the concatenation was to an nvarchar(max) variable. Evidently that's not the way it works! – Restore

Answer 3

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

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

to inspect the values of such variables in SSMS.

Restore

Answer 4

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

Syllabism