删除不在存储过程中工作

I used the following command to execute the stored procedure, but the delete wont run. what is the problem?

我使用以下命令来执行存储过程，但删除不会运行。问题是什么？

exec sp_delete 'fruits', '''1.5'',''3.5'''

sp_delete

CREATE PROCEDURE [dbo].[sp_delete] 
@stock_type VARCHAR(255) 
,@weight VARCHAR(255)

AS 

IF @stock_type ='fruits'
BEGIN TRY
IF EXISTS(SELECT * FROM tblshop WHERE quantity='1' and weight in ( @weight))
    BEGIN
        DELETE FROM tblshop WHERE quantity='1' and weight in ( @weight )
    END
END TRY
BEGIN CATCH
END CATCH

2 个解决方案

#1

Make it dynamic:

让它动态：

CREATE PROCEDURE [dbo].[sp_delete] 
@stock_type VARCHAR(255) 
,@weight VARCHAR(255)
AS 
declare @sql as nvarchar(max)
IF @stock_type ='fruits'
BEGIN TRY
set @sql = '
IF EXISTS(SELECT * FROM tblshop WHERE quantity=''1'' and weight in ( ' + @weight + '))
    BEGIN
        DELETE FROM tblshop WHERE quantity=''1'' and weight in ( ' + @weight + ' )
    END'
exec(@sql)
END TRY
BEGIN CATCH
END CATCH

and call it as [Update inline with comments]

并将其称为[更新内联评论]

exec sp_delete 'fruits', '1.5,3.5'

#2

You need to split @weight value into an array-like

您需要将@weight值拆分为类似数组

and then WHERE quantity='1' and weight in dbo.splitFunc(@weight)

然后WHERE quantity ='1'和dbo.splitFunc（@weight）中的权重

How to pass an array into a SQL Server stored procedure

如何将数组传递给SQL Server存储过程

#1