Conversion failed when converting varchar to int

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 2005 Forums

Transact-SQL (2005)

Conversion failed when converting varchar to int

Author

Topic

shulink
Starting Member

12 Posts

Posted - 2008-01-14 : 00:20:49

I have the a store procedure, and its giving me error message.
CREATE PROCEDURE [dbo].[DeleteMessages]
(@UserId uniqueidentifier,
@MessageIds varchar(150)
)
AS
DELETE FROM dbo.Messages
WHERE UserId = @UserId AND MessageId IN (@MessageIds)

I want to do something like
delete from Message where MessageId IN (1,2);
where I can delete multiple items. But When I put this in the store procedure, it produces the following error.

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '3,2' to data type int.

Does anyone know how to fix it? Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 01:10:37

It recognises the param only as a string. you need to split the comma seperated values and return them as int for filtering. One method will be to call a UDF which will take string as param and return as a table of integer values and take join with it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-14 : 01:12:09

DELETE FROM dbo.Messages
WHERE UserId = @UserId AND ','+@MessageIds+',' like '%,'+cast(MessageId as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

shulink
Starting Member

12 Posts

Posted - 2008-01-14 : 02:05:08

thanks a lot, this a great idea by adding commas at the beginning and ending of the letter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources