dynamic SQL, computed column, unicode, WTF?
-
G'day,
Here is a bit of an interesting situation. Here are three short code examples, nearly identical. This is a cut-and-paste from query analyzer. The situation is a stripped-down example. The first two snippets fail with the error messages as shown. The third snippet works. The only difference between shippet 2 and shippet 3 is that I placed my cursor after [ColName3] ), pressed delete twice and enter once. This leads me to believe that some invisible character is getting into the dynamic string. I am extremely familiar with generating dynamic SQL, with computed columns, and with Unicode. This is the first time I have tried to combine all three. My questions:
Has anyone seen this behavior before? What is the explanation for why is it occurring? What is the solution? My goal is to generate the sql string dynamically. How can I do this and not encounter the errors? On to the code snippets:
drop table WayneTest
DECLARE @SqlCmd VARCHAR(4000),
@Error INT
SET @SqlCmd =
'
CREATE TABLE [WayneTest] (
[ColName1] VARCHAR (10) NOT NULL ,
[ColName2] VARCHAR (20) NOT NULL ,
[ColName3] AS RIGHT(REPLICATE(''X'',30)+ CAST([ColName1] AS NVARCHAR) ,30)
CONSTRAINT [PK_WayneTest] PRIMARY KEY CLUSTERED ( [ColName3] )
 
'
PRINT '<SQLCmd1>' + @SQLCmd + '</SQLCmd1>'
-- EXEC @Error = SP_EXECUTESQL @SQLCmd
EXEC(@SqlCmd)
IF @Error <> 0 SELECT @Error
Server: Msg 8111, Level 16, State 2, Line 2
Cannot define PRIMARY KEY constraint on nullable column in table 'WayneTest'.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
DECLARE @SqlCmd NVARCHAR(4000),
@Error INT
SET @SqlCmd =
N'
CREATE TABLE [WayneTest] (
[ColName1] VARCHAR (10) NOT NULL ,
[ColName2] VARCHAR (20) NOT NULL ,
[ColName3] AS RIGHT(REPLICATE(''X'',30)+ CAST([ColName1] AS NVARCHAR) ,30)--
CONSTRAINT [PK_WayneTest] PRIMARY KEY CLUSTERED ( [ColName3] )
 
'
PRINT '<SQLCmd2>' + @SQLCmd + '</SQLCmd2>'
EXEC @Error = SP_EXECUTESQL @SQLCmd
--EXEC(@SqlCmd)
IF @Error <> 0 SELECT @Error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.
DECLARE @SqlCmd NVARCHAR(4000),
@Error INT
SET @SqlCmd =
N'
CREATE TABLE [WayneTest] (
[ColName1] VARCHAR (10) NOT NULL ,
[ColName2] VARCHAR (20) NOT NULL ,
[ColName3] AS RIGHT(REPLICATE(''X'',30)+ CAST([ColName1] AS NVARCHAR) ,30)--
CONSTRAINT [PK_WayneTest] PRIMARY KEY CLUSTERED ( [ColName3] )
)
'
PRINT '<SQLCmd3>' + @SQLCmd + '</SQLCmd3>'
EXEC @Error = SP_EXECUTESQL @SQLCmd
--EXEC(@SqlCmd)
IF @Error <> 0 SELECT @Error
query batch completed
-
When writing Dynamic SQL you have two choices to avoid those problems:
1) Do not break the string into several lines
2) If you must break it use explicit CRLF Terminator at each line end
Cheers,
Noel
* Noel -
Thanks Noel,
Your advice got me moving in the right direction. I was concatenating three NVARCHAR strings to create the complete command. I converted the code generator to use a single nvarchar string all the way through. That resolved the first issue completely.
The second issue still exists, but I have a feasible workaround.
Even though the base columns are "NOT NULL", the computed column thinks that it is nullable. As such, I am not permitted to create a primary key constraint on the computed column. However, I am able to create a unique constraint. Since the base column is "not null", this provides the net effect of a primary key. Seems like this is a bug in the SQL server parser/analyzer. If ColumnA is "not null", then 'X' + ColumnA must also be "not null".
Thanks for the suggestion. It helped break my mental block.
Have a good day
Wayne
-
Correct!
I was just refering to the syntax error issue. Good point to keep in mind about the NOT NULL, You can overcome the issue by using ISNULL function
DECLARE @SqlCmd NVARCHAR(4000),
@Error INT
SET @SqlCmd =
N'
CREATE TABLE [WayneTest] (
[ColName1] VARCHAR (10) NOT NULL ,
[ColName2] VARCHAR (20) NOT NULL ,
[ColName3] AS ISNULL(RIGHT(REPLICATE(''X'',30)+ CAST([ColName1] AS NVARCHAR) ,30),''XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX''),
CONSTRAINT [PK_WayneTest] PRIMARY KEY CLUSTERED ( [ColName3] )
)'
--
PRINT '<SQLCmd3>' + @SQLCmd + '</SQLCmd3>'
--EXEC @Error = SP_EXECUTESQL @SQLCmd
EXEC(@SqlCmd)
IF @@Error <> 0 SELECT @Error
-- DROP TAble [WayneTest]
Not Pretty but it works!
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply