Varying # of parameters sent to stored procedure 1

[sndkick]

Is there a way to send a stored procedure the number of parameters it will accept, and then send that number of parameters?

For instance, I have the following table
TABLE: tbl_Item
FIELDS: ID (auto incrementing), partNumber, serialNumber

I am looking for a stored procedure that would look sort of like this:
sp_AddSerialNumbers strPartNumber, SN1, SN2, SN3, ..., SNn

I want to be able to INSERT a list of records which will all have the same 'partNumber', but a bunch of different 'serialNumbers'. The information will be coming in from an ASP web app, and I would like to find a way to send all of the information ( a part number, and a list of serial number strings ) to a stored procedure. Is there a way to have a stored procedure count the number of variables being sent, or is there a way to send a stored procedure the number of parameters it is about to receive?

I know I can just use a loop in my application that will create the variable number of INSERT statements, but I would like to do it with a stored procedure.

Can this be done?
Any other ideas?

 

[TomSark]

Instead of doing this, why cant you call a proc and send the serial number one at a time which would require one SN argument? Just call the proc over and over until all SNs have been processed...?

Tom

 

[sndkick]

Yes that is what I had considered. But I was just wondering if anyone knew of a way to do it all in one stored procedure. I couldn't find a way to do it all in a stored procedure, but I figured I'd see if maybe somebody knew how, or if anyone knows that it is just not possible.

Scott

 

[TomSark]

Well, all parameters at the end of a proc that are set to have default values can be optional as in:

create procedure usp_tst
(
@strpartnum varchar(10),
@sn1 int = null, -- optional, defaults to null
@sn2 int = null, -- optional, defaults to null
-- ... and so on
)
as

-- some sql code here

return 0

So.. if @sn1 gets a value, it won't be null, but will be what you passed it. You will have to include the maximum number of parms you may need in the proc definition, but you can make the ones you don't need optional by virtue of assigning them a default value. Make sense?

Tom

 

[sndkick]

Yes that does make sense. That would work. There would still be a limit, but I could just include a large number of null-able parameters, and if the number of parameters i need to bring in exceeds what I have allowed, then I could make another call to the stored procedure to process the rest.

That should work. I'll giver 'er a try.
Thanks Tom

Scott

 

[TomSark]

Or... you could create a table that holds the info and processes the info that way instead of passing the data as parms to a proc...

Tom

 

[PeaceMaker]

yes, thats greate, i actually implement this in my SQL SERver 6.5 stored procedure...

create procedure usp_tst
(
@strpartnum varchar(10),
@sn1 int = null, -- optional, defaults to null
@sn2 int = null, -- optional, defaults to null
-- ... and so on
)


the other thing you would do is that create a table in the sql server ... and insert the records in that table

Use the machine name as identifier as records will be added by different users at the same time


then in your stored procedure
create a temporary #table and select all the records into that temporary table for your machine name... and there u have the unlimited number of parameters...

and efficient fast method ....

Ajay