Initializing stored procedure output parameter

Question

I have a simple SQL Server stored procedure:

ALTER PROCEDURE GetRowCount

(
@count int=0 OUTPUT
)

AS
Select * from Emp where age>30;
SET @count=@count+@@ROWCOUNT;

RETURN

I am trying to initialize and access the output parameter in the following C# code:

SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=answers;Integrated Security=True";

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;

cmd.CommandText = "GetRowCount";
cmd.Parameters.Add(new SqlParameter("@count", SqlDbType.Int));
cmd.Parameters["@count"].Direction = ParameterDirection.Output;
con.Open();
cmd.Parameters["@count"].Value=5;
cmd.ExecuteNonQuery();

int ans = (int)(cmd.Parameters["@count"].Value);
Console.WriteLine(ans);

But on running the code, an InvalidCastException is being thrown at the second last line of the code (I debugged and checked that nothing is being returned in the Value attribute).

How can I properly initialize the output parameter in code? Thanks in advance!

possible duplicate of Accessing SQL Server stored procedure output parameter in C# — podiluska
– podiluska, Commented Aug 29, 2012 at 10:33
I am the one who asked that question just about an hour ago, but this is a completely different problem — ankit0311
– ankit0311, Commented Aug 29, 2012 at 10:35

Community · Accepted Answer · 2017-05-23 12:12:19Z

6

You need to change the ParameterDirection to ParameterDirection.InputOutput

Have a look here for an example: how to get return value from procedure sql server 2005 to c#

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Aug 29, 2012 at 10:34

StuartLC

108k18 gold badges225 silver badges295 bronze badges

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

Comments

Andomar · Accepted Answer · 2012-08-29 10:38:09Z

1

You don't have to initialize an output parameter. Use ParameterDirection.InputOutput to pass the value to the stored procedure.

The most likely cause for a type exception is that the procedure returns DBNull.Value. You can test that like:

var ans = cmd.Parameters["@count"].Value as int?;

answered Aug 29, 2012 at 10:38

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

Initializing stored procedure output parameter

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related