Passing multiple int parameters to sp

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)

Passing multiple int parameters to sp

Author

Topic

nmahesh567
Starting Member

2 Posts

Posted - 2007-08-11 : 22:20:20

I want to know how I can pass multiple integer parameters to stored procedure which will be used in “in” clause.

Following pseudo code does not work.

Create stored procedure spTest
@intParams int
As

Select * from tableName where ID in (@intParams)

End

Here I can not pass multiple parameters to above sp such as “exec spTest 1,2,3”. If I have to convert int parameter to string, how do I convert that string again to int so that I can user those values in above query?

linfangmin
Starting Member

1 Post

Posted - 2007-08-11 : 22:42:05

As far as I know , sqlserver stored procedure not allow array parameters,you can use the dynamic sql,like this
Create Procedure spTest
@intParams Varchar(50)
As
declare @sql varchar(1024)
set @sql = 'Select * from tableName where ID in (' + @intParams + ')'
exec(@sql)

Go

nmahesh567
Starting Member

2 Posts

Posted - 2007-08-11 : 23:09:30

I do not want to use dynamic query. I am looking for the solution by which I can convert string parameter to int values to be used in "in" clause.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-12 : 00:13:30

http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-13 : 02:03:47

Also refer these
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources