how to create stored procedure for this query?

I want write a stored procedure for this query!

我想为这个查询编写一个存储过程!

My query is:

我的查询是:

SELECT *
FROM [dbo].[Table_asbabbazi] 
WHERE
    name_product LIKE '%'+'ibm'+ '%' 
    AND first_price BETWEEN 5000 AND 100000  
    AND collection_1 = 'collection1'  
    AND id_state = 8

I wrote a dynamic stored procedure like this :

我写了一个像这样的动态存储过程:

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, final_date_view_shamsi, count_views, image_1 FROM dbo.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 '  
    set @PARAMS='@name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @collection_1 nvarchar(30),
    @id_state tinyint'

    EXECUTE sp_executesql @SQLstring, @PARAMS, @name_product, @first_price, @final_price, @collection_1, @id_state
END

This stored procedure worked but there is a problem: when set value for the name_product it shows one product or any product. I test query in SQL Server Management Studio and it works properly. But this query in stored procedure does not work properly. I think problem is in this row

此存储过程有效但存在问题:当name_product的设置值显示一个产品或任何产品时。我在SQL Server Management Studio中测试查询,它正常工作。但是存储过程中的此查询无法正常工作。我认为问题就在这一行

 if(@name_product != 'no name')
    set @SQLstring = @SQLstring + ' AND name_product  LIKE '''+ '%' + @name_product + '%' + ''''

Please help

2 个解决方案

#1

Change Your Query Like this:

更改您的查询像这样:

Use print @SQLstring for Debugging Query

使用print @SQLstring进行调试查询

DECLARE @SQLstring nvarchar(1000)
DECLARE @PARAMS nvarchar(1000)  
set @SQLstring = 'SELECT IDproduct,name_product,first_price,final_price,max_registered_price,
                 final_date_view_shamsi,
                   count_views,image_1 from dbo.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  '+CAST(@first_price as nvarchar(10))+'  AND  
'+CAST(@final_price as nvarchar(10))+''
if (@collection_1 != 'انتخاب کنید')
set @SQLstring = @SQLstring + ' AND collection_1 =  '''+@collection_1 +''' '
if (@id_state != 0)
set @SQLstring = @SQLstring + ' AND id_state = '+CAST(@id_state as nvarchar(10)) 
set @PARAMS='@name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @collection_1 nvarchar(30),
    @id_state tinyint'

print @SQLstring
EXECUTE sp_executesql @SQLstring,
@PARAMS,
@name_product,
@first_price,
@final_price,
@collection_1,
@id_state

#2

You have extra quotes in your like statement cahnge your statement like this

你的声明中有额外的引号可以像你这样声明你的声明

' AND name_product  LIKE ''%' + @name_product + '%'''

Here is your full SP

这是你的完整SP

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,
             final_date_view_shamsi,
               count_views,image_1 from dbo.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 ' 
set @PARAMS='@name_product nvarchar(50),
@first_price int,
@final_price int,
@collection_1 nvarchar(30),
@id_state tinyint'
EXECUTE sp_executesql @SQLstring,
@PARAMS,
@name_product,
@first_price,
@final_price,
@collection_1,
@id_state
END

#1