Incorrect syntax in dynamic sql

Question

While executing below dynamic SQL, I get an error

Incorrect syntax near ','

Code:

declare @site varchar(40) = ''

begin
    EXECUTE SP_EXECUTESQL SELECT TP.F_PRODUCT AS ID FROM PDF_MSDS_NEW TP WHERE TP.F_AUTHORIZED IN(1,3) AND TP.F_PRODUCT <>'' AND (TP.F_TEXT_CODE LIKE @SITE), @SITE NVARCHAR(10),SITE0001
end

Now i changed my above query.It is not showing any error message now but output is not coming.

CREATE TABLE PDF_MSDS_NEW(F_PRODUCT VARCHAR(50),F_AUTHORIZED INT,F_TEXT_CODE VARCHAR(100))

INSERT INTO PDF_MSDS_NEW VALUES('F0001',1,'SITE0001')

DECLARE
@SITE VARCHAR(100),
@statement nvarchar(max)
BEGIN
set @SITE='SITE0001'
set @statement ='SELECT TP.F_PRODUCT AS ID FROM PDF_MSDS_NEW TP WHERE TP.F_AUTHORIZED IN(1,3)AND TP.F_PRODUCT <>'''' AND (TP.F_TEXT_CODE LIKE @SITE)'
PRINT @statement
EXECUTE SP_EXECUTESQL @statement ,N'@SITE NVARCHAR',@SITE
END

sp_executesql has two or more parameters. The first two parameters should be of type NVARCHAR(X). Review the link for some examples. — TT.
– TT., Commented Apr 20, 2019 at 13:07
Now i changed my query.I posted my new query.Now error message is coming but output is not coming. — Ram
– Ram, Commented Apr 21, 2019 at 10:10

SS_DBA · Accepted Answer · 2019-04-23 17:06:48Z

2

Try this. I changed your dynamic SQL to accept the parameter into the Set statement.

CREATE TABLE PDF_MSDS_NEW(F_PRODUCT VARCHAR(50),F_AUTHORIZED INT,F_TEXT_CODE VARCHAR(100))

INSERT INTO PDF_MSDS_NEW VALUES('F0001',1,'SITE0001')

DECLARE
@SITE VARCHAR(100),
@statement nvarchar(max)
BEGIN
set @SITE='SITE0001'
set @statement ='SELECT TP.F_PRODUCT AS ID FROM PDF_MSDS_NEW TP WHERE TP.F_AUTHORIZED IN(1,3)AND TP.F_PRODUCT <>'''' AND (TP.F_TEXT_CODE LIKE ''' + @SITE + ''')'
PRINT @statement
EXECUTE SP_EXECUTESQL @statement 
END

answered Apr 23, 2019 at 17:06

SS_DBA

2,4211 gold badge13 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Incorrect syntax in dynamic sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related