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 · Accepted Answer · 2017-11-29 10:45:42Z

2

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

edited Nov 29, 2017 at 10:45

answered Nov 29, 2017 at 10:38

Tom

15.6k2 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

SqlKindaGuy Over a year ago

This is not an answer.

Eliseo Jr Over a year ago

@Tom : Thank you very much Tom, this is perfect.

Tom Over a year ago

@EliseoJr, No worries :)

Tom Over a year ago

@EliseoJr, If you are happy with this solution please could you mark this as the answer? Thank you :)

Eliseo Jr Over a year ago

@Tom, how to mark this as answer? Sorry I do not know how to.

|

Collectives™ on Stack Overflow

SQL Server Dynamic Query Error

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related