Multipule stored variables in a stored procedure

Question

I have three variables that may or may not need to be passed on to a stored procedure, basically an all option and then options based by breaking it down by a few values. I have the the procedure if someone chooses the break down options and selects all the options individually but then I would have to make a GetReportbySV, GetReportbyVE, etc. Is there anyone to do this all in one stored procedure of would I be better off with 8 of them?

create proc GetReportbySVE
@SCode int,
@VCode int, 
@ECode int
as 
  begin
    select * from D 
    where S =  @SCode and V = @VCode and E = @ECode
end

edit: I am using a C# winform to pass the data to this procedure.

A language in which stored procedures are written is specific to each DBMS. Please indicate your DBMS via a tag. — Branko Dimitrijevic, Jul 12 '13 at 21:48

score 0 · Answer 1 · edited May 23 '17 at 11:49

It can be quite tough if you have even more parameters. Please have a look at this question on SO for further investigation: How can I use optional parameters in a T-SQL stored procedure?.

C# Example

I would never use a stored procedure for such a scenario. Instead, have a glance at the following code:

var sqlSelect = "Select field1, field2, field3 FROM yourTable"
var cmd = new SqlCommand(yourConn)

var sqlWhere = new StringBuilder();

If (sCode > 0)
{
cmd.Parameters.Add("@SCode", SqlDbType.int).Value = sCode;
sqlWhere.Append("@SCode AND");
}
If (vCode > 0)
{
cmd.Parameters.Add("@VCode ", SqlDbType.int).Value = vCode;
sqlWhere.Append("@VCode AND");
}

If(eCode > 0)
{
cmd.Parameters.Add("@ECode", SqlDbType.int).Value = eCode ;
sqlWhere.Append("@ECode AND");
}

if (sqlWhere.length > 0)
{
 sqlWhere.Insert("WHERE ",0);
}
cmd.CommandText = sqlSelect + sqlWhere.ToString();

using(var sr = SqlDataReader = cmd.ExecuteReader())
{
  //read your fields
}

score 0 · Answer 2 · answered Jul 12 '13 at 22:10

Stored Procedure

I would recommend you to use my other answer. However, here you also got a stored procedure example.

Anyway, the following procedure illustrates of how it would look like (just for two parameters, to give you an idea):

CREATE PROCEDURE GetReportbySVE
@SCode int,
@VCode int
AS

/* check for the NULL / default value (indicating nothing was passed */
if (@SCode = 0 AND @VCode = 0)
BEGIN
    /* your sql to execute*/
END
if (@VCode = 0)
BEGIN
    /* your sql to execute*/
END
if (@SCode = 0)
BEGIN
    /* your sql to execute*/
END
if (@SCode <> 0 AND @VCode <> 0)
BEGIN
    /* your sql to execute*/
END

Multipule stored variables in a stored procedure

2 Answers2

C# Example

Stored Procedure