variable parameter for sql parameters

Question

I'm accessing a stored procedure and I'm trying to pass some parameters to it. I have the following loop that adds all parameters:

List<string> paramNames = new List<string> { 
        "@EmployeeId", 
        "@Location", 
        "@StartYear", 
        "@EndYear", 
        "@Department", 
        "@Title", 
        "@FileName"  
};

List<string> paramValues = new List<string> { 
        EmployeeId, 
        Location, 
        StartYear, 
        EndYear, 
        Department, 
        Title, 
        FileName 
};

// Add the input parameter and set its properties.
SqlParameter parameter;
for (int i = 0; i < paramNames.Count(); i++)
{
   parameter = new SqlParameter();
   parameter.ParameterName = paramNames[i];
   parameter.Value = paramValues[i];
   command.Parameters.Add(parameter);
}

the first parameter is "EmployeeId" and this value is coming from the method arguments. This parameter can either be a string value "All" or it can be an int value with the employee id.

-How do I pass its value to the stored procedure so it accepts a string or an int.

-And how do I tell it that if its "All" then I want all employees, otherwise if its a number, I want the employee with that number ID?

As a side note, instead of maintaining two lists, use Dictionary<string,string> for your parameters and their values, or better List<SqlParameter> where you can also specify the type of parameters. — Habib
– Habib, Commented Feb 18, 2015 at 18:13

Comfortably Numb · Accepted Answer · 2015-02-18 18:11:55Z

1

Most likely your stored procedure has a condition similar to

WHERE EMPLOYEE_ID = @EmployeeId

Change it to

WHERE @EmployeeId = 'All' OR EMPLOYEE_ID = @EmployeeId

This is a 'catch all' condition. If you pass 'All' - first part will be true and condition passes. Otherwise value of the parameter will be verified against table field

answered Feb 18, 2015 at 18:11

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin · Accepted Answer · 2015-02-18 18:13:20Z

1

Instead of using "All" you could simply just use a value of -1 or 0 for EmployeeId and then check for 0 or -1 in your stored procedure to return all users in that scenario.

answered Feb 18, 2015 at 18:13

Justin

694 bronze badges

Collectives™ on Stack Overflow

variable parameter for sql parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related