• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

Passing variable number of parameters to SP and UDF

Hi Experts,

How can we make a SP or UDF to accept variable number of paramters, same like we pass to SP_ExecuteSql and Coalesce()?

Thanks,
Imran
0
imrancs
Asked:
imrancs
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can put optional parameters:

create proc <procname>
( @param1 int
, @param2 int = NULL
, @param3 int = NULL
)


0
 
batchakamalCommented:
Either,
pass the values as a delimited text, then do the separation inside the stored procedure using temporary table.

OR

the best way is to use XML. Send the parameters as a XML Document, then using OPENXML you can manipulate it.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Continuing Angels Comments


create proc <procname>
( @param1 int
, @param2 int = NULL
, @param3 int = NULL
)


call like

ProcName @param1 = 10, @param3 =10  
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
imrancsAuthor Commented:
Thanks guys for your feedback, but this is not what I am looking for. Is there any other solution other than the above mentioned?

Imran
0
 
imrancsAuthor Commented:
How the SP_ExecuteSql and Coalesce() are implemented?

Imran
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>How the SP_ExecuteSql and Coalesce() are implemented?
well; those are internal procedures (developed in c).

I don't see what is wrong about the optional parameters, as some system stored procedure work the same way..
0
 
imrancsAuthor Commented:
angelIII, there is nothing wrong with optional parameters except you need to know Max number of parameters and may be the data type too.

I was just looking for the way if could be done simply in t-sql.

Imran
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>except you need to know Max number of parameters and may be the data type too.
I know what you want to get at, a PARAMARRAY parameter like in VB, giving to the procedure code an array of the values passed.
But, helas, no such thing in SQL. the closest thing is the XML parameter
0
 
imrancsAuthor Commented:
Ok, Thanks angelIII and aneeshattingal for your help.

Imran
0
 
imrancsAuthor Commented:
Can you please give me some example to how to use XML for the above?

Imran
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
imrancsAuthor Commented:
Thanks again angelIII.

Imran
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are welcome
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now