Incorrect syntax near' error while executing dynamic sql

Question

I use dynamic sql for read products in my project. I want with the number for statment create sql statment my code is :

 CREATE PROCEDURE dbo.Asbabbazi_A
    @name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @max_registered_price int, 
    @collection_1 nvarchar(30),
    @id_state tinyint,
    @first smallint,
    @final smallint AS
begin
DECLARE @SQLstring nvarchar(1000)
DECLARE @PARAMS nvarchar(1000); 
set @SQLstring = 'SELECT IDproduct,name_product,first_price,final_price,max_registered_price,date_record_shamsi,final_date_view_shamsi,
                   count_views,image_1,collection_1 from Table_asbabbazi where active=0'
if(@name_product != "no name")
@SQLstring = @SQLstring + 'AND (name_product  LIKE '%'+(@name_product)+'%')'
 if (@finalPrice != 0)
 @SQLstring = @SQLstring +  'AND ( first_price between  @first_price AND @final_price )'
  if (subCollection != "انتخاب کنید")
 @SQLstring = @SQLstring + 'AND (collection_1=@collection_1  )'
 if (state != 0)
 @SQLstring = @SQLstring + 'AND (id_state=@id_state  )'
 set @PARAMS ='   @name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @max_registered_price int, 
    @collection_1 nvarchar(30),
    @id_state tinyint,
    @first smallint,
    @final smallint '
     Execute sp_Executesql @SQLstring,
      @name_product ,
    @first_price ,
    @final_price ,
    @max_registered_price , 
    @collection_1 ,
    @id_state ,
    @first ,
    @final 
    end

    RETURN

Error message content is:

Incorrect syntax near '@SQLstring'.

Must declare the scalar variable "@finalPrice".

Incorrect syntax near '@SQLstring'.

It dont work and show error message please help

Your posting looks like a mess -clean up. And if you can't get us any error message minimize your query, i.e. remove a small part of your query and try again etc, until you've found the problem. — jarlh, Feb 13 '15 at 14:40
if (subCollection != "انتخاب کنید") and if(@name_product != "no name"), why does this have double quote instead of single? There are a lot of other things that needs to be fixed as well. Have you checked this at all? I also cannot see you using SET right before the @SQLString. You should take a look at MSSQL language syntax. — Hozikimaru, Feb 13 '15 at 14:42

score 0 · Answer 1 · answered Feb 13 '15 at 14:47

Among other possible errors, You need to add spaces either to the beginning or end (or both) of all strings to make sure they don't collide.

As it stands this block:

set @SQLstring = 'SELECT IDproduct,name_product,first_price,final_price,max_registered_price,date_record_shamsi,final_date_view_shamsi,
                   count_views,image_1,collection_1 from Table_asbabbazi where active=0'
if(@name_product != "no name")
@SQLstring = @SQLstring + 'AND (name_product  LIKE '%'+(@name_product)+'%')'

will collide the active=0 at the end of the first string and AND at the beginning of the next string:

... active=0AND ...

score 0 · Answer 2 · edited May 23 '17 at 11:59

I believe you should definitely check SQL syntax for MSSQL. You have a couple of errors. First of all, why are you using double quotes for the following?

if (subCollection != "انتخاب کنید") 

if(@name_product != "no name")

Second of all, why don't you have SET right before @SQLString?

Try the following;

 CREATE PROCEDURE dbo.Asbabbazi_A
    @name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @max_registered_price int, 
    @collection_1 nvarchar(30),
    @id_state tinyint,
    @first smallint,
    @final smallint AS
begin
DECLARE @SQLstring nvarchar(1000)
DECLARE @PARAMS nvarchar(1000); 
set @SQLstring = 'SELECT IDproduct,name_product,first_price,final_price,max_registered_price,date_record_shamsi,final_date_view_shamsi,
                   count_views,image_1,collection_1 from Table_asbabbazi where active=0'
if(@name_product != 'no name')
 SET @SQLstring = @SQLstring + 'AND (name_product  LIKE '%'+(@name_product)+'%')'
 if (@final_price != 0)
 SET @SQLstring = @SQLstring +  'AND ( first_price between  @first_price AND @final_price )'
 if (subCollection != 'انتخاب کنید')
 SET @SQLstring = @SQLstring + 'AND (collection_1=@collection_1  )'
 if ([state] != 0)
 SET @SQLstring = @SQLstring + 'AND (id_state=@id_state  )'
 set @PARAMS ='   @name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @max_registered_price int, 
    @collection_1 nvarchar(30),
    @id_state tinyint,
    @first smallint,
    @final smallint '
     Execute sp_Executesql @SQLstring,
      @name_product ,
    @first_price ,
    @final_price ,
    @max_registered_price , 
    @collection_1 ,
    @id_state ,
    @first ,
    @final 
    end

    RETURN

You should definitely read about SQL.

Also, please see the single vs double quote and their usages;

What is the difference between single and double quotes in SQL?

Explain that double quotes are for identifiers (table names, column names etc), and single quotes are for character strings. — jarlh, Feb 13 '15 at 14:50
@jarlh I would assume this would be known. Writing a stored procedure in SQL is a level 300 class. But will do :) — Hozikimaru, Feb 13 '15 at 14:56

score 0 · Answer 3 · answered Feb 13 '15 at 14:48

You can see just from the syntax highlighting that there is a problem. For instance, in this line:

@SQLstring = @SQLstring + 'AND (name_product  LIKE '%'+(@name_product)+'%')'

The '%' character is not part of any string. You need additional single quotes:

@SQLstring = @SQLstring + 'AND (name_product  LIKE ''%''' + @name_product + '''%'')'

This may be the only problem. Or not. If not, I would suggest that you start as simply as possible select <col> from <table> and incrementally add to the code to spot problems one by one. The quoting on dynamic SQL can be tricky.

score 0 · Answer 4 · edited Dec 18 '15 at 17:57

I change my code, and it works now:

CREATE PROCEDURE  dbo.Asbabbazi_A
    (@name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @collection_1 nvarchar(30),
    @id_state tinyint)
AS
BEGIN
DECLARE @SQLstring nvarchar(1000)
DECLARE @PARAMS nvarchar(1000)  
set @SQLstring = 'SELECT IDproduct,name_product,first_price,final_price,max_registered_price,
                  date_record_shamsi,final_date_view_shamsi,
                   count_views,image_1,collection_1 from Table_asbabbazi where active=0 '
if(@name_product != 'no name')
set @SQLstring = @SQLstring + ' AND (name_product  LIKE %@name_product%)'
if (@final_price != 0)
set @SQLstring = @SQLstring +  ' AND ( first_price between  @first_price AND @final_price )'
if (@collection_1 != 'انتخاب کنید')
set @SQLstring = @SQLstring + ' AND (collection_1=@collection_1  )'
if (@id_state != 0)
set @SQLstring = @SQLstring + ' AND (id_state=@id_state  )'

execute @SQLstring
END

Incorrect syntax near' error while executing dynamic sql

4 Answers4