Passing parameter to store SQL procedure ???

[Guest]

Dear all,

I am passing parameters to store procedure without any troubles but
parameters list are less than 10 parameters, still manageable.

But we have a situation where we do not know for instance the number of
variable my final customer would like to monitor and store to database.
It can be a list of 20 variabls or 50 or more...

I was wondering what is the best way to pass an unknown list of parameters
to a store procedure , can we use arrays ? XM files ?

one parameter will correspond to one table field at the end

What is the best way to do it and how ?
thanks for help

regards
serge

 

[Robbe Morris - [MVP] C#]

Uh, if you don't know the variables, how do you know which column
to put the value in?

No, sql server doesn't have the concept of arrays for stored
procedure parameters. Yes, you could pass in XML but I suspect
that you'll quickly find that is a painful alternate based on your
requirements.

The only way I can think of is that you are using dynamic sql
in your stored procedure. If that is the case, then why bother
with passing it to a stored procedure at all? You could just
package up the sql in your .NET code instead.

 

[William Vaughn]

SQL Server 2008 introduces Table-valued parameters that might help, but
there are ways that you can pass structured data to a SP in SQL Server 2005
(or 2000 for that matter--but it's harder). For example, you can pass a
delimited string that has multiple values or a structure of your own
choosing that can feed a logic engine to process the query. In 2005 you can
write a SQL CLR stored procedure to parse the structure or write it in TSQL.

Another approach (as was indicated by another contributor) is to create
dynamic SQL. This is possible, but can be dangerous as it can lead to SQL
injection attacks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)