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 sqlperformance.com/2012/10/t-sql-queries/sp_prefix — GuidoG
– GuidoG, Commented Oct 13, 2017 at 14:49
SQL Server does not support macro substitution ... weight in ( @weight)) — John Cappelletti
– John Cappelletti, Commented Oct 13, 2017 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
– Magnus, Commented Oct 13, 2017 at 14:52
You need to pass the different values as an array to the procedure, look at this example stackoverflow.com/questions/11102358/… — GuidoG
– GuidoG, Commented Oct 13, 2017 at 14:53

Amit Kumar Singh · Accepted Answer · 2017-10-13 15:23:10Z

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, 2017 at 15:23

Amit Kumar Singh

4,4952 gold badges11 silver badges24 bronze badges

answered Oct 13, 2017 at 14:56

cloudsafe

2,5241 gold badge11 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SS_DBA Over a year ago

Why is this different from what they have already? You need to split the comma separated values into a table.

cloudsafe Over a year ago

@WEI_DBA This works. Just don't pass the superfluous single quotes i.e: exec sp_delete 'fruits', '1.5,3.5'

Amit Kumar Singh Over a year ago

@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.

SS_DBA Over a year ago

@cloudsafe. Ah. Didn't see the parms passing in. Thanks!

Daniel B · Accepted Answer · 2017-10-13 14:56:58Z

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, 2017 at 14:56

Daniel B

3,2152 gold badges38 silver badges48 bronze badges

Collectives™ on Stack Overflow

Delete not working in stored procedure

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related