the name is not a valid identifier. error in dynamic stored procudure

my stored procudure is :

ALTER 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

when execute show this error: The name ‘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 AND (name_product LIKE %@name_product%) AND (collection_1 = @collection_1 )’ is not a valid identifier. please help

Answer

Some parameters in a query string are not parsed correctly, and you are using dynamic sql it must be executed by EXECUTE sp_executesql statement. This is the correct way to execute dynamic sql:

ALTER 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  ' + CONVERT(nvarchar(1000), @first_price)  + ' AND ' + CONVERT(nvarchar(1000), @final_price) + ' '
if (@collection_1 != 'انتخاب کنید')
set @SQLstring = @SQLstring + ' AND collection_1 = ''' + @collection_1 + ''' '

if (@id_state != 0)
set @SQLstring = @SQLstring + ' AND id_state = ' + CONVERT(nvarchar(1000), @id_state) + ' '

EXECUTE sp_executesql @SQLstring
END