Delete not working in stored procedure

Question

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

dont give procedures"sp_" as prefix http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — GuidoG, Oct 13 '17 at 14:49
and here https://msdn.microsoft.com/en-us/library/ms190669(v=sql.105).aspx#Naming%20Stored%20Procedures — GuidoG, Oct 13 '17 at 14:50
SQL Server does not support macro substitution ... weight in ( @weight)) — John Cappelletti, Oct 13 '17 at 14:50
Also there is no need to first check if it exists an than delete it. Only records that exists will be deleted anyway. — Magnus, Oct 13 '17 at 14:52
You need to pass the different values as an array to the procedure, look at this example https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure — GuidoG, Oct 13 '17 at 14:53
@JohnCappelletti i tried to put it in as `@str='delete from tblshop where weight in (' +@weight+')'` as well, and `exec @str` but it didnt work. — whoami, Oct 13 '17 at 14:54
yeah I would not expect the delete to happen if you pass 'AC' for the first parameter. — Tab Alleman, Oct 13 '17 at 14:57
If you need to pass your data in as a comma delimited string then you can't use the parameter with the IN clause like that. In order to make this work, you will ned to first split your list into a temp table or use a table valued function that takes your lists and returns a table, one record per element. Once you have your @weight in a table format then you can simply use --> IN(SELECT Value FROM dbo.FunctionToTurnListIntoTable(@Weight)) — Ross Bush, Oct 13 '17 at 14:58
@JohnCappelletti sorry, it was fruits. wrongly typed the parameters. — whoami, Oct 13 '17 at 15:00

score 3 · Answer 1 · edited Oct 13 '17 at 15:23

3

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'

edited Oct 13 '17 at 15:23

Amit Kumar Singh

4,393
2
9
22

answered Oct 13 '17 at 14:56

cloudsafe

2,444
1
8
24

Why is this different from what they have already? You need to split the comma separated values into a table. – SS_DBA Oct 13 '17 at 14:57
@WEI_DBA This works. Just don't pass the superfluous single quotes i.e: exec sp_delete 'fruits', '1.5,3.5' – cloudsafe Oct 13 '17 at 15:21
@cloudsafe Query need not check IF EXISTS before DELETE statement, because DELETE will not delete any row if there is none. Otherwise, query has to delete it anyways. For the same reason, TRY CATCH is also not required here. – Amit Kumar Singh Oct 13 '17 at 15:25
@cloudsafe. Ah. Didn't see the parms passing in. Thanks! – SS_DBA Oct 13 '17 at 15:31

score 1 · Answer 2 · answered Oct 13 '17 at 14:56

1

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

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

How to pass an array into a SQL Server stored procedure

answered Oct 13 '17 at 14:56

Daniel B

3,109
2
33
42

Delete not working in stored procedure

2 Answers2