Stored Procedure Parameter Datatype Convertion

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)

Stored Procedure Parameter Datatype Convertion

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-08 : 07:37:25

padmanaban writes "my procedure is

--=================================
CREATE PROC spProc (@dtDate as datetime,@Ids as varchar(100))
AS

SELECT * FROM test WHERE Sid IN (@ids)

--==================================

Sid Column in the table Test is Numeric(9) Datatype

my problem is

if i execute proc like this its show the error

exec spProc '04/01/2006','131,132'
error msg:
error converting datatype nvarchar to numeric

but at the same time below code is working fine

exec spProc '04/01/2006','131'

how its work? and how can i retrieve the data for muliple sids?"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-08 : 08:55:09

[code]
CREATE PROC spProc (@dtDate as datetime,@Ids as varchar(100))
AS
declare @sql varchar(8000)
select @sql = 'SELECT * FROM test WHERE Sid IN (' + @ids + ')'
exec (@sql)
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-08 : 12:48:14

or

Where ','+@ids+',' like '%,'+cast(id as varchar(20))+',%

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources