Creating a SP with unlimited parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a SP with unlimited parameters

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-08-12 : 08:15:40

Hello All,
I want to create a function that should take unlimited parameters.
Is it possible to create a function or Sp like that?

Waiting for your kind reply

regards
Yasir Mehmood

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-12 : 08:23:45

http://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 08:26:58

unlimited parameters? why do you want to do that? can you elaborate on your reqmnt?

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-08-12 : 08:30:50

Actually I have to write a parser Function which will get My Query and All parameters and it will parse the query and place the values of each parameter and a complete query will be sent back

like
Function(Querry, Param1,Param2 ,.....)
{
}

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-12 : 08:31:12

See varchar(max) & nvarchar(max). You could convert your params to a single param & back again inside the proc. See http://www.sommarskog.se/arrays-in-sql-2005.html (Courtesy of Sunil)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 08:33:30

quote:
Originally posted by khufiamalik

Actually I have to write a parser Function which will get My Query and All parameters and it will parse the query and place the values of each parameter and a complete query will be sent back

like
Function(Querry, Param1,Param2 ,.....)
{
}

you can pass the parameter values as a comma seperated list if they all represent same field values and use udf to parse them. You can find couple of them inside these forums

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-12 : 14:51:02

I've not tried it myself, but you might be able to pass in XML and parse it. Or you might be able to use a delimited list as suggested above.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources