One function for multiple stored procedures C#

Question

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

An abstraction like this typically takes a collection of `SqlParameter`, or, it takes a collection of data that is mapped to parameters that are obtained dynamically. Your example is clearly contrived so it is hard to tell exactly what you need. — Crowcoder, Jun 27 '16 at 15:35
@Crowcoder I have multiple stored procedures that take different arguments, hut I want to call them from one function, thats all. As such, I want to add all the parameters for all the stored procedures within the function. What I need then is for the each stored procedure to accept only parameters that it needs. — MattDev, Jun 27 '16 at 15:46
Can you put your stored procedure code and explain what you need, because this is unclear and hard to answer your question. — Hitesh, Jun 27 '16 at 15:48
You can obtain parameter data from this view: `sys.parameters`. But you will need your method (not function) to receive a data structure that has information you need to map. The Enterprise Library Data Access Application Block uses a simple HashTable with param name and value. — Crowcoder, Jun 27 '16 at 15:55

score 0 · Answer 1 · edited Oct 01 '16 at 07:57

0

Can you please share the stored procedure code? I think you need to use if else or case statement in your function and call appropriate stored procedure with required parameters inside your function.

edited Oct 01 '16 at 07:57

marc_s

732,580
175
1,330
1,459

answered Jun 27 '16 at 15:55

Saurabhchauhan232

89
2
15

score 0 · Answer 2 · edited May 23 '17 at 12:15

Make your function take two arguments, the name of the SP and a 2d array of SP argument names and their corresponding values. Then set up your sql command by iterating over the 2d array and adding the parameters in the loop.

If you need to return several result sets, see here: How do I return multiple result sets with SqlCommand? and modify the function and args so that they take an array of SP names and an array of 2d arrays, containing the SP arg names and values for each SP.

score 0 · Answer 3 · answered Jun 27 '16 at 16:04

I think you should use this structure :

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS

/* check for the NULL / default value (indicating nothing was passed */
if (@Parameter3 IS NULL)
BEGIN
    /* whatever code you desire for a missing parameter*/
    INSERT INTO ........
END

/* and use it in the query as so*/
SELECT *
FROM Table
WHERE Column = @Parameter

score 0 · Accepted Answer · edited Jun 27 '16 at 16:27

0

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

edited Jun 27 '16 at 16:27

Sumurai8

20,333
11
66
100

answered Jun 27 '16 at 16:22

ODelfyette

121
3

One function for multiple stored procedures C#

4 Answers4