delete in list SP - SQL Server Forums

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

Transact-SQL (2000)

delete in list SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-01-27 : 18:41:16

I'm trying to create the SP below, but I am having problems. How can I accomplish this? I am passing a list of values comma delimited.

Example

24322,23425,23452

Thanks for any insight.

Mike

CREATE PROCEDURE [delete_tblMessage_List]
(
@MessageIDTXT VarChar(1000),
@MessageTo [int]
)
AS SET NOCOUNT ON

DELETE [tblMessage]

WHERE ( [MessageID] IN @MessageIDTXT AND [MessageTo] = @MessageTo )

GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-27 : 18:50:17

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-01-27 : 19:45:08

thanks rob,

this is what I have so far. I'm having problems with adding the second parameter. Do you think I should just pass the whole query as the text? Would this be a performance hit?

Thanks for the help

Mike123

CREATE PROCEDURE [delete_tblMessage_List]
(
@MessageIDTXT VarChar(1000),
@MessageTo [int]
)
AS SET NOCOUNT ON

Declare @SQL VarChar(1000)

Select @SQL = 'DELETE [tblMessage] '
Select @SQL = @SQL + 'WHERE ( [MessageID] IN (' + @MessageIDTXT +') AND [MessageTo] '= @MessageTo

Exec ( @SQL)

GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-27 : 19:51:58

You had an opening parenthesis in the WHERE clause without a closing one, try this:

CREATE PROCEDURE [delete_tblMessage_List]
(
@MessageIDTXT VarChar(1000),
@MessageTo [int]
)
AS SET NOCOUNT ON

Declare @SQL VarChar(1000)

Select @SQL = 'DELETE [tblMessage] '
Select @SQL = @SQL + 'WHERE [MessageID] IN (' + @MessageIDTXT +') AND [MessageTo] '= @MessageTo
Exec ( @SQL)

For a fairly simple AND condition like this one, you don't need the parentheses to wrap up the entire WHERE clause, and it's better not to unless you absolutely have to group logical conditions together. Adding parentheses unnecessarily can make the query optimizer do funky things, especially if you're tweaking the conditions constantly. MS Access is a good example of parentheses gone haywire; many times an Access query won't work in SQL Server even if it is syntactically correct, or it WILL work but will perform badly because of nested parentheses/groups.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-01-27 : 20:23:21

thanks for the tip rob!

I still get this msg tho:

would it have something to do with the second parameter being an INT?

Cheers
Mike123

Server: Msg 170, Level 15, State 1, Procedure delete_tblMessage_List, Line 11
Line 11: Incorrect syntax near '='.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-27 : 20:36:30

$%#%!@ not paying attention to code, try this:

CREATE PROCEDURE [delete_tblMessage_List]
(
@MessageIDTXT VarChar(1000),
@MessageTo [int]
)
AS SET NOCOUNT ON

Declare @SQL VarChar(1000)

Select @SQL = 'DELETE [tblMessage] '
Select @SQL = @SQL + 'WHERE [MessageID] IN (' + @MessageIDTXT +') AND [MessageTo] = ' + CAST(@MessageTo As varchar)
Exec ( @SQL)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-01-27 : 21:39:30

beautiful, thanks again

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources