SQL Server Dynamic Query Error

Question

Need your help to figure out what is my mistakes using dynamic SQL Query. Below is my sample query, but I cannot detect where are my mistakes.

DECLARE @p_orgcompreq NVARCHAR(10)='1.1' 
DECLARE @table_name NVARCHAR(50)='test' 
DECLARE @treelevel INT 
DECLARE @OrgCompCode_Parent NVARCHAR(10)='1.1' 
DECLARE @OrgCompCode_Child NVARCHAR(10)='1.1.1' 
DECLARE @SQLQuery NVARCHAR(max) 

SELECT @treelevel = @@NESTLEVEL - 1 

SET @SQLQuery = 'insert into ' + @table_name 
                + 
' (level,OrgCompCode_Parent,OrgCompCode_Child) values (replicate(CHAR(45), ' 
                + Ltrim(Str(@treelevel)) 
                + ' * 1)          + ltrim(str(' 
                + Ltrim(Str(@treelevel)) + ')),''' + ( @OrgCompCode_Parent ) + 
                ''',''' + 
                                @OrgCompCode_Child + ')' 

EXEC (@SQLQuery) 

SELECT @SQLQuery

And below are the raised error. Hope someone could help me. Thanks.

Msg 105, Level 15, State 1, Line 2 Unclosed quotation mark after the character string '1.1.1)'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '1.1.1)'.
(1 row(s) affected)

Tom Tom · Accepted Answer · 2017-11-29T10:38:12

You can check this by running a Print

DECLARE @p_orgcompreq NVARCHAR(10)='1.1' 
DECLARE @table_name NVARCHAR(50)='test' 
DECLARE @treelevel INT 
DECLARE @OrgCompCode_Parent NVARCHAR(10)='1.1' 
DECLARE @OrgCompCode_Child NVARCHAR(10)='1.1.1' 
DECLARE @SQLQuery NVARCHAR(max) 

SELECT @treelevel = @@NESTLEVEL - 1 

SET @SQLQuery = 'insert into ' + @table_name 
                + 
' (level,OrgCompCode_Parent,OrgCompCode_Child) values (replicate(CHAR(45), ' 
                + Ltrim(Str(@treelevel)) 
                + ' * 1)          + ltrim(str(' 
                + Ltrim(Str(@treelevel)) + ')),''' + ( @OrgCompCode_Parent ) + 
                ''',''' + 
                                @OrgCompCode_Child + ')' 

Print @SQLQuery

This will show you the following message:

insert into test (level,OrgCompCode_Parent,OrgCompCode_Child) values (replicate(CHAR(45), -1 * 1)          + ltrim(str(-1)),'1.1','1.1.1)

Edit

Looking at the error this is the section where the error is @OrgCompCode_Child + ')' and as such the following should resolve this:

DECLARE @p_orgcompreq NVARCHAR(10)='1.1' 
DECLARE @table_name NVARCHAR(50)='test' 
DECLARE @treelevel INT 
DECLARE @OrgCompCode_Parent NVARCHAR(10)='1.1' 
DECLARE @OrgCompCode_Child NVARCHAR(10)='1.1.1'''
DECLARE @SQLQuery NVARCHAR(max) 

SELECT @treelevel = @@NESTLEVEL - 1 

SET @SQLQuery = 'insert into ' + @table_name 
                + 
' (level,OrgCompCode_Parent,OrgCompCode_Child) values (replicate(CHAR(45), ' 
                + Ltrim(Str(@treelevel)) 
                + ' * 1)        + ltrim(str(' 
                + Ltrim(Str(@treelevel)) + ')),''' + ( @OrgCompCode_Parent ) + 
                ''',''' + 
                                @OrgCompCode_Child + ')'

OR

DECLARE @p_orgcompreq NVARCHAR(10)='1.1' 
DECLARE @table_name NVARCHAR(50)='test' 
DECLARE @treelevel INT 
DECLARE @OrgCompCode_Parent NVARCHAR(10)='1.1' 
DECLARE @OrgCompCode_Child NVARCHAR(10)='1.1.1'
DECLARE @SQLQuery NVARCHAR(max) 

SELECT @treelevel = @@NESTLEVEL - 1 

SET @SQLQuery = 'insert into ' + @table_name 
                + 
' (level,OrgCompCode_Parent,OrgCompCode_Child) values (replicate(CHAR(45), ' 
                + Ltrim(Str(@treelevel)) 
                + ' * 1)        + ltrim(str(' 
                + Ltrim(Str(@treelevel)) + ')),''' + ( @OrgCompCode_Parent ) + 
                ''',''' + 
                                @OrgCompCode_Child +''''  + ')'

print @SQLQuery
EXEC (@SQLQuery) 

SELECT @SQLQuery

SQL Server Dynamic Query Error

1 Answers