Get Output Parameter Value in Ado.Net

How to get output parameter and also a table when executing a stored procedure

The parameter value won't be available until after you consume the resultset, eg

var cmd0 = new SqlCommand("create or alter procedure pFoo @id int output as begin  select * from sys.objects; set @id = 12; end", con);
cmd0.ExecuteNonQuery();

var cmd = new SqlCommand("pFoo", con);
cmd.CommandType = CommandType.StoredProcedure;

var p1 = cmd.Parameters.Add("@id", SqlDbType.Int);
p1.Direction = ParameterDirection.Output;


var dt = new DataTable();
using (var rdr = cmd.ExecuteReader())
{                    
    dt.Load(rdr);
    var id = (int)p1.Value;
}

Get Output Parameter Value in C#

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.

Integer out parameter null issue in ADO.NET while calling through procedure

@output must be ParameterDirection.InputOutput if you want it to use the initial value set within the client.

Currently as it's ParameterDirection.Output the value is ignored, it defaults to NULL within the procedure and NULL + anything results in a NULL.

Get output value in ADO.Net without stored procedure

The newly inserted ID from your OUTPUT Inserted.ID_PRIMARY will be a data set being returned from the statement. You need to use a ExecuteReader approach to read those values returned:

' setup as above - except you don't need the "output_parameter"
Dim reader As SqlDataReader = insert_command.ExecuteReader()

While reader.Read()
    ' get your newly inserted ID's here as returned dataset
    Dim newlyInsertedID As Integer = reader.GetInt32(0)

    ' if you insert multiple rows at once, you might need store those ID's 
    ' that you fetch back one by one into a list or something
End While

Retrieve output parameter of SQL statement

First, use the ExecuteNonQuery to write operations. After execute command, you can read the parameter from Parameters collection, since you have set the parameter as a output parameter, for sample:

command.Parameters["name"].Direction = System.Data.ParameterDirection.Output;

command.ExecuteNonQuery();

object name = command.Parameters["name"].Value;

If you want to know what Id the identity column generated after the insert, you could use SCOPE_IDENTITY() sql command, and use the ExecuteScalar() to get the result of command, for sample:

int id;
using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
{
    string sql = @"INSERT INTO custom_customer (customer_name) 
                                        VALUES (@name); 
                   SELECT SCOPE_IDENTITY();"

    using (SqlCommand command = new SqlCommand(sql))
    {
        command.Parameters.Add(new SqlParameter("name", customer));
        connection.Open();
        id = (int) command.ExecuteScalar();
        connection.Close();
    }
}

C# & SQL Server stored procedures - can't get output parameter value, return value works

Not sure if you can assign count(*) to a parameter. It only works in the sql statement.

Perhaps try this

Create procedure sp_CountEachNumberOccurencesOutput
    @number tinyint, @count_number int output
    As
    SET NOCOUNT ON
    select @counter_number=count(*)  from RawData
                where Number1 = @number or Number2 = @number  or Number3 = @number

How to get values of output parameters in c# while executing SP using ExecuteScalar()

try like this when you define output parameters:

         associateParams[8] = new SqlParameter("@userSalt", SqlDbType.VarChar, 400);
         associateParams[8].Value = "";
         associateParams[8].Direction = ParameterDirection.Output;
         associateParams[9] = new SqlParameter("@activationKey", SqlDbType.Int);                 
         associateParams[9].Value = 0;
         associateParams[9].Direction = ParameterDirection.Output;

let me know if this helps.

UPDATE: here is my own method

 cmd.Parameters.Add(new SqlParameter("@userSalt", SqlDbType.VarChar, 400));
 cmd.Parameters["@userSalt"].Value = "";
 cmd.Parameters["@userSalt"].Direction = ParameterDirection.Output;

UPDATE1: because you dont use ExecuteNonQuery. Change cmd.ExecuteScalar() to cmd.ExecuteNonQuery()

---

Related Topics