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

Multiple parameters in a Select statement at run time

What is the best way to format a Select statement when the amount of parameter variables are not known until run time?
0
fly412s
Asked:
fly412s
  • 2
1 Solution
 
DarkXiphoidCommented:
Make an arraylist with parameters. Like this string[] paramList = new string[]

That way you can add any number of parameters during runtime.

Hope this helps you in the right direction.

Regards,

Robert
0
 
dstanley9Commented:
Are you talking about a SQL SELECT statement?  AFAIK it's not possible.  The best way I've seen is to use a finite set of VARCHAR parameters:

CREATE PROCEDURE MyParameterHack
@param1 VARCHAR(500) = null,
@param2 VARCHAR(500) = null,
@param3 VARCHAR(500) = null,
@param4 VARCHAR(500) = null,
@param5 VARCHAR(500) = null,
@param6 VARCHAR(500) = null
AS
...

However, SQL does support "optional" parameters, which may be a better option.

What is the procedure going to do?  Maybe there's an alternate method than variable parameters



0
 
fly412sAuthor Commented:
I'm using ADO.net.  I'm using the System.Data.OleDb namespace.  I'm connecting to a flat database file (.csv).  
0
 
dstanley9Commented:
Then you can build it using string concatenation.

For example, to use a dynamic WHERE clause, use arrays for field names and values:

string[] fields;
string [] values;

// build arrays of fields and values

string sql = "SELECT * FROM flatfile WHERE " 
for(int i=0; i<numParams; i++)
{
  sql += "[" + fields[i] + "] = @Param" + i.ToString() ;
  if (i<numparams-1)
    sql += " AND ";
}
OleDbCommand command = new OleDbCommand(sql);

for(int i=0; i<numParams; i++)
{
  command.Parameters.Add("@Param" + i.ToString(),values[i]);
}



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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