How to send string-named arguments to a stored procedure?

Question

I have a stored procedure with a large number of parameters (>50), the vast majority of which are optional. I would like to be able to call it like this:

var result = context.MySproc(
               new Dictionary<string, string>()
               {
                   {"foo","bar"},
                   {"baz","xyzzy"}
               });

The parameters needed will vary dynamically.

The DB call must be a stored procedure (not up to me), and it must use the existing DataContext rather than setting up a new connection.

I'd sincerely appreciate if you could just leave a comment saying, if my approach worked for you or not. I did myself a similar task in another programming language, and just wonder whether same approach suited in C# via Linq to SQL — horgh
– horgh, Commented Sep 28, 2012 at 3:57

horgh · Accepted Answer · 2012-10-03 13:16:25Z

If I understood you correctly, than this is the article telling how to call a ms sql stored procedure with optional parameters. And this is how I used it to call such stored proc with LINQ to SQL:

1) Suppose you have a stored proc with optional parameters like:

CREATE PROCEDURE [dbo].[MyProc] 
    @arg1 bigint,
    @arg2 datetime,
    @arg3 decimal(18,2)=null,
    @arg4 decimal(18,2)=null,
    @arg5 int
BEGIN
...
END

2) You have some DataContext using LINQ to SQL

DataContext dc = new DataContext("Your connection string, for example");

3) Stored proc name

string procName = "dbo.MyProc";

4) Params dictionary (for example):

Dictionary<string, object> paramList = new Dictionary<string, object>()
        {
            {"arg1",72},
            {"arg2",DateTime.Now.Date},
            //arg3 is omitted and should get its default value in stored 
            {"arg4",250},  proc
            {"arg5",777}
        };

5) Then you may use the following method:

    static int Foo(DataContext dc, 
                   string procName, 
                   Dictionary<string, object> paramList)
    {
        StringBuilder command = new StringBuilder("EXECUTE ");
        command.Append(procName);

        int i = 0;

        foreach (string key in paramList.Keys)
        {
            command.AppendFormat(" @{0} = ", key);
            command.Append("{");
            command.Append(i++);
            command.Append("},");
        }

        return dc.ExecuteCommand(command.ToString().TrimEnd(','),  
                                 paramList.Values.ToArray());
    }

like this

//You should add exception handling somewhere, of course, as you need    
Foo(dc, procName, paramList);

It will invoke your stored procedure. Compulsory params should always be present in the dictionary or it will fail. Optional parameters may be omitted, then they'll get the default values, defined by the stored procedure itself.

I used Dictionary<string,object>, so it may contain not only string values, but any type of parameters. Of course, they should reflect what the stored procedure expects.

P.S.: I tested on ms sql 2008, I'm not completely sure, how it'll work on ms sql 2005

Francis P · Accepted Answer · 2012-09-26 17:48:07Z

0

In SQL Server, the number of parameters must be static, so you won't be able to do what you want.

You have some other solutions:

1: Use 1 delimited string as a parameter and then substring the parameter in your stored proc.
2: Save those 50 or so strings in a table (attached to a unique ID), use that table from your stored procedure (using the unique ID as the only parameter) and then make your stored procedure delete those temporary strings.

answered Sep 26, 2012 at 17:48

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:04:38Z

0

The only way to do this would be creating an xml file and send it to your proc. You can fetch all parameters within sql.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Sep 26, 2012 at 18:01

Andreas Rehm

2,23217 silver badges20 bronze badges

1 Comment

Kirk Broadhurst Over a year ago

Or a CSV or other delimited data string.

Collectives™ on Stack Overflow

How to send string-named arguments to a stored procedure?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related