Passing a BIT value to a sql server stored procedure

user_1180438 0
Viewed 859

I have a stored procedure:

CREATE PROCEDURE myproc(@a nvarchar(50), @b nvarchar(100), @c bit)
AS ...

   SELECT @retVal;

I have the following aspx code:

SqlCommand cmd = new SqlCommand("myproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@a", a);
cmd.Parameters.AddWithValue("@b", b);
cmd.Parameters.Add("@c", SqlDbType.Bit).Value = false;

SqlParameter returnValue = new SqlParameter("@retVal", SqlDbType.NVarChar, 9999999);
returnValue.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnValue);
conn.Open();
cmd.ExecuteNonQuery();
retVal = (string)cmd.Parameters["@retVal"].Value;   
conn.Close();

This gives me the error:

Procedure or function myproc has too many arguments specified.

If I remove the line:

cmd.Parameters.Add("@c", SqlDbType.Bit).Value = false;

I get this error:

Procedure or function 'myproc' expects parameter '@c', which was not supplied

I have tried these alternatives for this line:

cmd.Parameters.AddWithValue("@c", 0);
cmd.Parameters.AddWithValue("@c", false);

How do I pass that BIT parameter?

sql-server-2012 sql-server c#
3 Answers

I present the 3 ways we can get data out of a procedure:

Here is an sp that returns a value

CREATE PROCEDURE x AS
BEGIN
  return 1
END

You have to get that 1 in your c# by adding a parameter with direction ReturnValue and type int (it can only be an int), and reading the parameter .Value after you ExecuteNonQuery

Typically we use returnvalue to indicate the state of the operation, NOT some data in the table. Eg an AddUser proc that returns 0 for success, 1 for fail because user exists, 2 for fail because password is too weak etc, we wouldn't use returnvalue to return the userid of the newly added user

Here is an sp that has an output parameter

CREATE PROCEDURE x
    @y INT OUTPUT
AS BEGIN
  SET @y = 1
END

You have to get that 1 in c# by adding a parameter with name @y and direction Output and read the parameter value after you ExecuteNonQuery. If you use the parameter for input as well, declare the direction as InputOutput

Here is an sp that returns a resultset

CREATE PROCEDURE x
AS BEGIN
  SELECT 1 AS y
END

You have to get that 1 by doing ExecuteScalar or ExecuteReader

ExecuteScalar returns the top left cell of a resultset; fine for this example. If you plan on returning more rows and columns than just a single value, use ExecuteReader

It sounds like you're mixing things up; decide how your sp will return its values and code the c# accordingly.

You can mix and match these and have a sproc that has an output parameter, a return value and selects a resultset but you should be clear on which bits of the c# collect what data from which bits of the sproc

user_14106640

Your stored procedure doesn't define an output parameter. @c isn't your problem, the problem is @retVal.

You need something like this:

CREATE PROCEDURE myproc(@a nvarchar(50), @b nvarchar(100), @c bit, @retVal NVARCHAR([LEN]) OUTPUT)
AS...

...where [LEN] is whatever you need, I doubt it's 9,999,999, but you can do MAX if you want.

Then store [YOUR RESULT] in your shiny new output parameter:

SELECT @retVal = [YOUR RESULT]

...at this point I believe your C# code will behave correctly.

user_10487990

Try to change this:

    returnValue.Direction = ParameterDirection.Output;

to this:

    returnValue.Direction = ParameterDirection.ReturnValue;

OK, if the @retVal is not integer, then you can't use ParameterDirection.ReturnValue. Stay with SELECT @retVal, but change cmd.ExecuteNonQuery() to var result = Convert.ToString(cmd.ExecuteScalar()); and also don't define @retVal parameter at C#.

user_130831590
Related