How to pass variable params to sp??

How to pass variable params to sp??

Post by MIKE KISE » Tue, 22 Jun 1999 04:00:00



Hello!
I have a stored procedure in which I need to pass a variable number of
parameters to the sp.  How can this be achieved? For example, I query
for a one to many relationship and however many entities there are, I
need to pass them to the stored procedure.  If I have 3 StudyID's to 1
PatientID, for example I need to pass the 3 StudyID's.  Next time there
might be only 1 StudyID passed to the stored procedure....any
suggestions?

Thanks!

Mike Kiser
Prucka Engineering, Inc.

 
 
 

How to pass variable params to sp??

Post by Mark Sulliva » Tue, 22 Jun 1999 04:00:00


Mike,

    If you know the maximum number of arguments (4 in my example) that could
be passed using the following:


<your code go here>

When executing this procedure the only mandatory field is patientId.

Mark


Quote:

> Hello!
> I have a stored procedure in which I need to pass a variable number of
> parameters to the sp.  How can this be achieved? For example, I query
> for a one to many relationship and however many entities there are, I
> need to pass them to the stored procedure.  If I have 3 StudyID's to 1
> PatientID, for example I need to pass the 3 StudyID's.  Next time there
> might be only 1 StudyID passed to the stored procedure....any
> suggestions?

> Thanks!

> Mike Kiser
> Prucka Engineering, Inc.


 
 
 

How to pass variable params to sp??

Post by Buddy Ackerma » Tue, 22 Jun 1999 04:00:00


You must define your parameters when you create the stored procedure.
Probably the best way to do what you want is to store the variable parameter
(StudyID) in a (temporary) table and run the stored proc retrieving the
parameters from the table.

--Buddy


Quote:

> Hello!
> I have a stored procedure in which I need to pass a variable number of
> parameters to the sp.  How can this be achieved? For example, I query
> for a one to many relationship and however many entities there are, I
> need to pass them to the stored procedure.  If I have 3 StudyID's to 1
> PatientID, for example I need to pass the 3 StudyID's.  Next time there
> might be only 1 StudyID passed to the stored procedure....any
> suggestions?

> Thanks!

> Mike Kiser
> Prucka Engineering, Inc.

 
 
 

How to pass variable params to sp??

Post by Glenn Crow » Tue, 22 Jun 1999 04:00:00


Mike,

    There are several ways to deal with situation.  I think it would
probably be best in your case to pass your original predicate to the second
sp.  You obviously have a query that returns a unknown number of rows.  The
best way to pass that result set to the second sp is the pass the query that
builds the resultset.

--
Please reply to the newsgroup.
Thanks!
glennc

Quote:

> Hello!
> I have a stored procedure in which I need to pass a variable number of
> parameters to the sp.  How can this be achieved? For example, I query
> for a one to many relationship and however many entities there are, I
> need to pass them to the stored procedure.  If I have 3 StudyID's to 1
> PatientID, for example I need to pass the 3 StudyID's.  Next time there
> might be only 1 StudyID passed to the stored procedure....any
> suggestions?

> Thanks!

> Mike Kiser
> Prucka Engineering, Inc.

 
 
 

1. passing params in an sp returns error saying the sp has no params

I am trying to run the following query with the idea of returning the status
variable. But when I include the 1st parameter.append statement below I get the
error "Procedure spMASolicitudStatus has no parameters and arguments were
supplied". The sp is listed after this snippet of code.  If anyone has any ideas
I would be greatly indebted.

Thanks in advance

sSolicitudID = cint(Request.QueryString("solicitud"))
cmd.CommandText = "spMASolicitudStatus"
cmd.CommandType =  iStoredProc          
cmd.Parameters.Append cmd.CreateParameter("iSolicitud",adInteger,,, sSolicitudID)
cmd.Parameters.Append cmd.CreateParameter("iStatus",adVarChar,adParamOutput,50)      
cmd.Execute
vPgStatus = cmd("iStatus")    

Here is the stored parameter spMASolicitudStatus:
CREATE PROCEDURE [spMASolicitudStatus] AS





The line in question is the ist append parameter line. This returns the following
error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure spMASolicitudStatus has
no parameters and arguments were supplied.

2. Cost of converting from DOS to Windows

3. How to Exec a SP where SP name and params are alll variables

4. Passing Parameters to DTS package

5. Passing Params to SP easily

6. Interesting: Lookup instead of aggregate at all levels?

7. problem passing params to SP's

8. odbc Enroll tutorial problem..???

9. OPENROWSET passing local variables as params

10. IN CODE: Send DYNAMIC Params to SP, then export SP to .txt

11. Passing a variable for tablename in a SP

12. Passing Variable to SP from Access

13. Create Table Using Passed Variables to SP