MattDev MattDev - 2 months ago 9
SQL Question

One function for multiple stored procedures C#

I have a function that executes stored procedures. The thing is, I wish to use this one function for multiple stored procedures that take different arguments.

Of course, if I do so, I will get an error saying that


Procedure or function has too many arguments specified


What I want to do is to create the function such that when it executes a stored procedure, that stored procedure only takes the parameters that it needs and ignore the others. Is this possible?

Here is what I have so far:

try
{
using (SqlConnection con = new SqlConnection(consr))
{
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = stp;
cmd.Parameters.Add(new SqlParameter("@proc1", cmb1.SelectedItem.ToString()));
cmd.Parameters.Add(new SqlParameter("@proc2", cmb2.SelectedItem.ToString()));
cmd.Parameters.Add(new SqlParameter("@proc3", cmb3.SelectedItem.ToString()));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
con.Open();

reader = cmd.ExecuteReader();

con.Close();
}
}
catch (SqlException exp)
{
throw new InvalidOperationException(exp.Message);
}


Here are two of the procedures:

ALTER PROCEDURE [dbo].[test1]
@proc1 varchar(20)

AS

Begin

select * from tab where name=@proc1

END


and

ALTER PROCEDURE [dbo].[test1]
@proc2 varchar(20)

AS

Begin

select * from tab where name=@proc2

END


I want to use the same function to execute both

Answer

You can set the params you don't want to use to null, then check for which values are available and respond accordingly

ALTER PROCEDURE [dbo].[test1]

@proc1 varchar(20) = null
@proc2 varchar(20) = null
@proc3 varchar(20) = null

AS 

Begin

IF @proc1 IS NOT NULL BEGIN

select * from tab where name=@proc1 

END 

END