May 3, 2006 at 8:14 pm
Hi all,
I am attempting to delete a number of records using one stored procedure and asp in SQL 2000.
My delete sp is:
CREATE PROCEDURE dbo.commentdelete
@vstrComments nvarchar(1000)
AS
SET NOCOUNT ON
DELETE FROM dbo.comments
WHERE commentID in (@vstrComments)
GO
The commentID's come from an asp page with checkboxes. if the user selects a checkbox, then the record associated with it needs to be deleted. However I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '2351,2352,2353' to a column of data type int.
I understand why I am getting the error, I guess my question is how do I actually achieve passing in a list of PK's to be deleted?
Thanks for your help,
regards,
Daniel
May 3, 2006 at 8:24 pm
You have two options that I can think of:
1. Parse the string (a bit of coding) to extract individual values and store it in a table variable (@tbl), then change your SQL to something like this:
DELETE FROM dbo.comments
WHERE commentID in (select CommentID from @tbl)
OR
2. use the EXEC() statement:
EXEC ( 'DELETE FROM dbo.comments WHERE commentID in (''' + @vstrComments + ''')' )
May 3, 2006 at 9:49 pm
Hi Paul,
Thanks for the post...The exec() option seems a lot easier....Is there a trade off for doing it that way? also, is a table variable basically an array in asp?
Thanks,
Daniel
May 3, 2006 at 10:31 pm
Yes there's a big tradeoff both is security and performance.
Check those out :
Arrays and Lists in SQL Server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply