Accepting Dynamic Size of Parameters in Stored Procs

[Al Davis]

Hello,

Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[jaganmohan rao]

You can have a maximum of 2100 INPUT or OUTPUT Parameters. SQL will accept name, data type, direction, and default value.

[albert davis]

Thanks for your reply, but what I was really trying to find out was how to send parameters to a stored proc that was unknown to the stored proc. This in turn would allow the stored proc to figure out how many params were sent dynamically (all using the same stored proc).

So if I had a stored proc called sp_mine and passed:

exec sp_mine "a", "b"

The stored proc would figure out that the caller has passed 2 params. And if I called the same stored proc and passed:

exec sp_mine "a", "b", "c", "d"

The stored proc would figure out that the caller has passed 4 params. Thus, this would allow the stored proc not to be "hard-coded" and would support any number of params sent...

Do you have any ideas how this would work?



------------
jaganmohan rao at 7/6/01 1:56:21 PM

You can have a maximum of 2100 INPUT or OUTPUT Parameters. SQL will accept name, data type, direction, and default value.

[jaganmohan rao]

You will have to define any INPUT or OUTPUT Parms with in your SP Like

Create Proc My_proc

@v1 int,
@v2 char(50),
@v3 int OUTPUT

as

--- Statements

The above procedure expects 2 input parms (one int and one char) and output one param.

exec My_proc 1, 'abc' will succeed
exec My_proc @v1, @v2 will succed assuming that you have declared them locally

exec My_proc 1 fails
exec My_proc 'abc' fails

If you supply more than 2 values, the SP ignores rest of the values.

[Patrick]

Hello Al,
Your questions sounds like wanting a maximum of flexibility. In number of parameters.
You could investigate the temporary table (# and ##) then you could have how many rows as you want and for the columns you could have the freedom offered by the software. Each column and row is able to be input and output to and from the procedure and the caller.
Then only one parameter could go to the called procedure, the name of the temporary table. With the name of the table, you could go to the sysobjects table then you could discover the structure of the columns etc.
Have fun
Patrick




------------
Al Davis at 7/6/01 1:24:20 PM

Hello,

Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al