Variable number of parameters to sProc?

Variable number of parameters to sProc?

Post by Rick Putna » Sat, 15 Dec 2001 12:12:21



There's got to be a better way...

I'm developing a sProc for SQL7: SELECT * FROM x WHERE
LocationID IN ([List of LocationIDs]).

I'd like to pass a variable number of IDs to the sProc
(who wouldn't?). When I use a delimited varchar parameter
and pass it to the SELECT statement I get an error.

Perhaps there's a better approach. Thanks in advance.

--Rick

 
 
 

Variable number of parameters to sProc?

Post by Umachandar Jayachandra » Sat, 15 Dec 2001 15:28:30


1) Parse the comma-separated string into a temporary table or use a UDF
(SQL2000) to split into a table. You can also parse the CSV string using a
SELECT statement. Look for Joe Celko's post on this
2) Pass the IDs as XML & use OPENXML to process it in SQL2000

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Variable number of parameters to sProc?

Post by kumarave » Sat, 15 Dec 2001 21:34:25


Rick ,

You can use dynamic sql.
eg:

create table t1(id1 int ,name varchar(10))
go
insert t1 select 1 ,'test1'
insert t1 select 2 ,'test2'
insert t1 select 3 ,'test3'
go


go
testproc '1,2'

HTH,
Kumar


Quote:> There's got to be a better way...

> I'm developing a sProc for SQL7: SELECT * FROM x WHERE
> LocationID IN ([List of LocationIDs]).

> I'd like to pass a variable number of IDs to the sProc
> (who wouldn't?). When I use a delimited varchar parameter
> and pass it to the SELECT statement I get an error.

> Perhaps there's a better approach. Thanks in advance.

> --Rick

 
 
 

Variable number of parameters to sProc?

Post by Scott Gordo » Sat, 15 Dec 2001 23:43:31


What about passing a cursor to the sp?  I've seen reference to this in BOL
but I never really understood how to use it.


Quote:> There's got to be a better way...

> I'm developing a sProc for SQL7: SELECT * FROM x WHERE
> LocationID IN ([List of LocationIDs]).

> I'd like to pass a variable number of IDs to the sProc
> (who wouldn't?). When I use a delimited varchar parameter
> and pass it to the SELECT statement I get an error.

> Perhaps there's a better approach. Thanks in advance.

> --Rick

 
 
 

1. Exec SProc from SProc (output parameter question)

I am tring to create a SProc that takess 2 Input Parameters and then based
on the result will execute another SProc which will use the first SProcs
result as an input parameter to the second. This second SProc will return 3
output parameters to my web application.

I have the second SProc working(code below) but am having trouble creating
the first one that will access the second one.

Second SPRoc
----------------




As
   Select




GO
-----------------

Some of my questions:
1.) In the first SProc Do I have to have the output parameters declared that
are being returned from the second SProc?
2.) How can I execute the second SProc suppling the InputParameter to it.

Help would be greatly appreciated,
Ron VEcchi

2. glossery

3. How to handle variable number of input parameters?

4. SQL Link drivers

5. Passing a variables number of parameters to a SP

6. problem with Informix backup

7. variable number of parameters

8. primary key

9. writing stored procedures that accept a variable number of parameters

10. Variable number of parameters in Stored Procedure

11. is this possible -> variable number of parameters

12. Variable number of parameters used in WHERE clause.

13. sp_executesql and variable number of parameters