Get Output Parameter Value in C#

Question

I have a stored procedure with an OUTPUT parameter:

CREATE PROCEDURE [dbo].[myStoredProcedure]
    (@myValue NVARCHAR(100) OUTPUT)
AS
BEGIN
    SET @myValue = (SELECT myValue FROM myTable WHERE something = 1)
    SElECT @myValue;
END

My C# class:

public string getOutPut()
{
    string shortFooter;
    string sql = "myStoredProcedure";
    string connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    using (SqlConnection DBConn = new SqlConnection(connStr))
    {
        using (SqlCommand DBCmd = new SqlCommand(sql, DBConn))
        {
            try
            {
                DBConn.Open();

                DBCmd.CommandType = CommandType.StoredProcedure;
                SqlParameter newSqlParam = new SqlParameter();
                newSqlParam.ParameterName = "@myValue";
                newSqlParam.SqlDbType = SqlDbType.NVarChar;
                newSqlParam.Direction = ParameterDirection.Output;
                DBCmd.Parameters.Add(newSqlParam);

                DBCmd.ExecuteNonQuery();
                shortFooter = DBCmd.Parameters["@myValue"].Value.ToString();
            }
            catch (Exception ex)
            {
                string blah = ex.ToString();
                shortFooter = "";
            }
        }
    }

    return shortFooter;
}

This is the exception that I'm getting:

System.InvalidOperationException: String[0]: the Size property has an invalid size of 0.

What I don't understand is, I have used this code before, except that in the stored procedure I'm getting the value of the output with IDENTITY() when I create a new record in the database, so the parameter type in the class is INT. But in concept it should be the same, but I guess I'm wrong, just don't know why.

What am I missing?

Thanks!!

the SElECT @myValue; in the query is useless on a DBCmd.ExecuteNonQuery();. — Jeroen van Langen
– Jeroen van Langen, Commented Apr 5, 2014 at 23:33

Grant Winney · Accepted Answer · 2014-04-05 23:40:20Z

5

You need to specify the size required to hold the output value. From MSDN:

For output parameters with a variable length type (nvarchar, for example), the size of the parameter defines the size of the buffer holding the output parameter.

Set the Size of the output parameter in your code to match what's in the stored procedure:

newSqlParam.Size = 100;

The default value is 0, which is what's causing the exception.

edited Apr 5, 2014 at 23:40

answered Apr 5, 2014 at 23:34

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

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

Collectives™ on Stack Overflow

Get Output Parameter Value in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related