Delete multiple rows in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete multiple rows in stored procedure

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-12 : 01:44:54

I am trying to delete multiple rows in the database. For example, i wish to delete ids "1,2,3" which is int data type in the database table. However, i got some error indicating trouble convert varchar to datatype integer.

I think it is because of this line of coding: "PROMOTIONID IN ('' + @PROMIDS + '')" If i do not put double single quotes, there is a syntax error. Anyone can help me out? I have included my stored procedure below:

CREATE PROCEDURE [DELETEPROMOTION]
(
@PROMOTIONIDS VARCHAR (100)
)

As

DECLARE @PROMIDS VARCHAR(100), @ITEMCOUNT INT

SELECT @PROMIDS= [Data]
FROM SPLIT(@PROMOTIONIDS, ',')
LEFT OUTER JOIN dbo.ITEMPROM
ON PROMOTIONID = Data
WHERE PROMOTIONID IS NULL
ORDER BY Data

DELETE FROM MSTRPROM
WHERE PROMOTIONID IN ('' + @PROMIDS + '')

GO

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 01:54:59

Use this

exec ( 'DELETE FROM MSTRPROM
WHERE PROMOTIONID IN (' + @PROMIDS + ')')

in place of
DELETE FROM MSTRPROM
WHERE PROMOTIONID IN ('' + @PROMIDS + '')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 02:23:13

or

DELETE FROM MSTRPROM
WHERE @PROMIDS like '%,'+PROMOTIONID+',%'

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-12 : 03:05:48

The coding you gave below does not work. Error msg:
Syntax error converting the varchar value '%,' to a column of data type int.

quote:
Originally posted by madhivanan

or

DELETE FROM MSTRPROM
WHERE @PROMIDS like '%,'+PROMOTIONID+',%'

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-12 : 03:07:01

I heard that using dynamic sql is not very good. Actually, i wish not to use dynamic sql if possible, unless left with no choice. Do you know how else i can work around this?

Thanks

quote:
Originally posted by shallu1_gupta

Use this

exec ( 'DELETE FROM MSTRPROM
WHERE PROMOTIONID IN (' + @PROMIDS + ')')

in place of
DELETE FROM MSTRPROM
WHERE PROMOTIONID IN ('' + @PROMIDS + '')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 03:07:20

Try

DELETE FROM MSTRPROM
WHERE @PROMIDS like '%,'+cast(PROMOTIONID as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-12 : 03:35:15

It does not work as well. Could not even delete 1 id.

quote:
Originally posted by madhivanan

Try

DELETE FROM MSTRPROM
WHERE @PROMIDS like '%,'+cast(PROMOTIONID as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 03:44:57

try this
DELETE FROM MSTRPROM
WHERE ','+ @PROMIDS + ',' like '%,'+cast(PROMOTIONID as varchar(10))+',%'

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-12 : 04:04:14

I tried that too but can only delete 1 id.

quote:
Originally posted by shallu1_gupta

try this
DELETE FROM MSTRPROM
WHERE ','+ @PROMIDS + ',' like '%,'+cast(PROMOTIONID as varchar(10))+',%'

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 04:41:51

print what exactly is there in @promids variable before deleting

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 05:29:27

Check this


Declare @t table(i int)
insert into @t 
select 12 union all select 2 union all select 142 union all select 127 

declare @values varchar(20)
set @values='2,12'
select * from @t
delete from @t where ','+@values+','  like '%,'+cast(i as varchar(10))+',%'
select * from @t

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-12 : 09:18:28

Promids store data such as "1,2,3"

quote:
Originally posted by shallu1_gupta

print what exactly is there in @promids variable before deleting

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-12 : 09:19:41

Sorry, but is there anything simpler than that? Need to hand this up for my assignment and its not believable that i have done this sql coding myself. Thanks

quote:

Originally posted by madhivanan

Check this


Declare @t table(i int)
insert into @t 
select 12 union all select 2 union all select 142 union all select 127 

declare @values varchar(20)
set @values='2,12'
select * from @t
delete from @t where ','+@values+','  like '%,'+cast(i as varchar(10))+',%'
select * from @t

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 03:12:55

Then use Dynamic SQL

EXEC('Delete from yourTable where id in ('+@ids+')')

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 04:51:56

What is the assignment requirement ? No Dynamic SQL ?

>> its not believable that i have done this sql coding myself.
Then you have to learn all the above syntax and understand them. There are multiple solutions to your question that apply various techniques. Ask questions if you do not understand.

-----------------
'KH'
if you can't beat them, have someone else to beat them

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-13 : 06:21:26

This was explained another member, thats why i think its better to not use dynamic sql.

There are two downsides to this (compared to the other approach that has been suggested), but that doens't
mean its wrong - just thought you might like to know!
1) The user has to have SELECT access to the MSTRITEM and ITEMIMAG tables. Without the dynamic SQL
they would only need EXECUTE permission on the SProc itself - which is obviously a lot tighter - with SELECT
permission they can just hook up, say, Excel to the DB and start pulling data at will ... and successful hackers
can do too!

quote:
Originally posted by khtan

What is the assignment requirement ? No Dynamic SQL ?

>> its not believable that i have done this sql coding myself.
Then you have to learn all the above syntax and understand them. There are multiple solutions to your question that apply various techniques. Ask questions if you do not understand.

-----------------
'KH'
if you can't beat them, have someone else to beat them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources