Read return parameters by name from stored procedure

Question

Is there a way to read some custom return parameters from a stored procedure by name in C#? The iterative way works like this:

SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
    while (rdr.Read())
    {
        if (rdr[0] != null)
        {
            string PARAM1 = rdr[0].ToString();
        }
        if (rdr[1] != null)
        {
            string PARAM2 = rdr[1].ToString();
        }
        if (rdr[2] != null)
        {
            string PARAM3 = rdr[2].ToString();
        }
        if (rdr[3] != null)
        {
            string PARAM4 = rdr[3].ToString();
        }
    }
}
rdr.Close();

the procedure returns something like this:

SELECT 
    'value1' AS 'PARAM1', 
    199 AS 'PARAM2', 
    'value2' AS 'PARAM3', 
    'value3' AS 'PARAM4'
RETURN 0

..but that solution is not satisfying to get a param by name or by type.

Do you really mean return parameters, meaning the parameter direction is ReturnValue? Or are you talking about the result set? — Crowcoder
– Crowcoder, Commented Jun 18, 2018 at 11:50
Can you explain what is not working the way you want? I can tell you that if the reader has more than one row you will overwrite the PARAMx variables on each loop iteration and that is probably not what you would want. — Crowcoder
– Crowcoder, Commented Jun 18, 2018 at 12:01
@UweKöhler you don't need a parameter you can access it by rdr["PARAM1"] directly — mbadeveloper
– mbadeveloper, Commented Jun 18, 2018 at 14:11
the access with parameter name: rdr[name] doesn't work because the name is unknown — Uwe
– Uwe, Commented Jul 9, 2018 at 11:46

mbadeveloper · Accepted Answer · 2018-06-18 12:08:54Z

1

Use the parameter name and get the value like this:

Param4 = rdr["@YourOutputParameter"].Value

May be you need to cast it to the type that you expected, for string use:

Param4 = rdr["@YourOutputParameter"].Value.ToString();

edited Jun 18, 2018 at 12:08

answered Jun 18, 2018 at 12:01

mbadeveloper

1,2709 silver badges16 bronze badges

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

2 Comments

Uwe Over a year ago

the access with parameter name doesn't work for me because the name is unknown. May be because it's not defined inside SQL as out parameter?

mbadeveloper Over a year ago

@UweKöhler make sure that the name of the column that you try to get the value or the column alias exist in the columns returned in the select statement

Collectives™ on Stack Overflow

Read return parameters by name from stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related