delete statement error using dynamic parameter

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 statement error using dynamic parameter

Author

Topic

m.abdullah
Starting Member

7 Posts

Posted - 2007-12-08 : 02:08:48

I am trying to delete from table carCategory where the carCategory Id = @id if is set or the carCategory Id is in parameters like where id in (1,2,3)
so i used where id in (@strIds)

but it gives me an error "Syntax error converting the varchar value '1,2,3' to a column of data type int"

and here is the code

CREATE PROCEDURE [dbo].[carCategoryDelete]

(

	@id int = NuLl,
             @strIds varchar(2000) = Null
)

	AS
if @id is not Null
Begin

	DELETE FROM 
		carCategory
	WHERE 
		Id = @id
End
Else
Begin
	declare @SQL as nvarchar(4000)
				
	SET @SQL = 'DELETE FROM carCategory'
	SET @SQL = @SQL + '  WHERE id in(@strIds)'
	EXEC SP_EXECUTESQL @SQL,  N'@strIds varchar(2000)', @strIds

End
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-08 : 02:42:48

[code]SET @SQL = @SQL + ' WHERE id in(' + @strIds + ')'[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources