Passing multiple values into one parameter in 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 2000 Forums

Transact-SQL (2000)

Passing multiple values into one parameter in SP

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-07 : 16:20:50

Passing multiple values into one parameter in SP

Is there any way I can pass multiple values into one @parameter?
I can’t just set @orgid = ( select orgid from org where or_type = ‘A’) because there are many orgid where or_type = ‘A’

For example,

Create proc A
@orgid int

As

select @orgid = ( select orgid from org where or_type = ‘A’)

select * from b
where org_id in (@org_id)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-07 : 16:23:03

Why can't you just do this:

select * from b
where org_id in (select orgid from org where or_type = 'A')

?

Tara

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-07 : 16:41:30

Yes, I know I can do it like that.

But, I want to figure out if there is a way I can pass mutiple vlaues into one parameter in SP.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-07 : 16:43:52

You can't put multiple values into a parameter. You'd have to do it one at a time with a WHILE loop or you could put the values in a csv string and put that string into the parameter.

http://www.sqlteam.com/item.asp?ItemID=11499
http://www.sqlteam.com/item.asp?ItemID=11021

Tara

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 04:06:28

You could perhaps turn it on its head and have the SProc use a function to get the actual data? i.e. where the Function is responsible for getting the appropriate data that you currently envisage being passed to the SProc [by the applicaiton]

Something like:


USE NOrthwind
go

CREATE TABLE org
(
	org_id	int,
	or_type	varchar(10)
)
GO

INSERT INTO org
SELECT	1, 'A' UNION ALL
SELECT	2, 'A' UNION ALL
SELECT	3, 'A' UNION ALL
SELECT	4, 'B' UNION ALL
SELECT	5, 'B'
GO

CREATE FUNCTION dbo.MyFunction
(
	@or_type	varchar(10)
)
RETURNS TABLE
AS
RETURN
	SELECT	org_id 
	FROM 	dbo.org
	WHERE 	or_type = @or_type
GO

CREATE PROCEDURE A 
	@or_type	varchar(10)
AS
SET NOCOUNT ON

	SELECT	* 
	FROM	dbo.org B
		JOIN dbo.MyFunction(@or_type) X
			ON X.org_id = B.org_id
GO

EXEC A 'A'
EXEC A 'B'
GO

DROP TABLE org
DROP PROCEDURE A
DROP FUNCTION dbo.MyFunction

Kristen

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-08 : 17:40:02

Can you some me some examples of using while loop to pass multiple values in SP?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-08 : 17:41:16

I certainly wouldn't recommend using a WHILE loop. It is a possibility to use, but I'd rather not give an example for something that can be done with a set-based solution. Did you have a look at the SQLTeam article links that I posted?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources