CODEWITHSUNDEEP
c#sql.netsql-serverstored-procedures
now he who must not be named.
now he who must not be named.

Reputation: 9396

Stored procedure does not output result

As a part of getting to learn Stored procedures , I came up with this.

CREATE PROCEDURE StoredProcedure1
@oResult int output
AS 
BEGIN
  SELECT @oResult = 2
  RETURN @oResult
END
  1. But the problem is on execution of this SP, it just returns something like. Procedure or function 'StoredProcedure1' expects parameter '@oResult', which was not supplied. I want this procedure just to return a result when called.

Any ideas why ?

  1. I supplied as it was asking,EXEC StoredProcedure1 @oResult = 0 but it just says Command Completed Successfully but no output.

Any ideas why ?

Upvotes: 0

Views: 1905

Answers (4)

Rahul wasan
Rahul wasan

Reputation: 1

CREATE PROCEDURE SP1
(
@oResult int output
)
AS 
BEGIN
       SET @oResult=2
       Select @oResult
END

Upvotes: 0

Arion
Arion

Reputation: 31249

You could do this:

Store procedure

CREATE PROCEDURE StoredProcedure1
@oResult int output
AS 
BEGIN
  SET @oResult = 2
END

And then calling it like this:

DECLARE @Result INT
exec StoredProcedure1 @oResult = @Result output
SELECT @Result

This will output

2

Update:

Like addressed in the comment. You could also simplify the statement. By doing this:

DECLARE @Result INT
exec StoredProcedure1 @Result output
SELECT @Result

Reference:

Upvotes: 2

Steve
Steve

Reputation: 216333

In ADO.NET when you call a Stored Procedure that expects a parameter, you need to give that parameter, also if it is an output parameter.

using(SqlConnection cnn = new SqlConnection(.....))
using(SqlCommand cmd = new SqlCommand("StoredProcedure1", cnn))
{
    cnn.Open();
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter p = new SqlParameter("@oResult", SqlDbType.Int);
    p.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(p);

    cmd.ExecuteNonQuery();

    int result = Convert.ToInt32(cmd.Parameters["@oResult"].Value);
}

Of course, in the stored procedure you should set the @oResult parameter in some way as explained in the other answers, but if you use an OUTPUT parameter there is no need to RETURN the same value.

However you could have both an OUTPUT parameter and a RETURN value if you need to. In this case your call from C# should be

using(SqlConnection cnn = new SqlConnection(.....))
using(SqlCommand cmd = new SqlCommand("StoredProcedure1", cnn))
{
    cnn.Open();
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter p = new SqlParameter("@oResult", SqlDbType.Int);
    p.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(p);

    // Here the name @returnValue is arbitrary, you could call it whatever you like
    SqlParameter r = new SqlParameter("@returnValue", SqlDbType.Int);
    r.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add(r);

    cmd.ExecuteNonQuery();

    int result = Convert.ToInt32(cmd.Parameters["@oResult"].Value);
    int returnValue = Convert.ToInt32(cmd.Parameters["@returnValue"].Value);
}

But keep in mind that RETURN values are limited to Integer Expressions.

Upvotes: 3

Neel
Neel

Reputation: 11741

You do not need to write return ..try below code :-

CREATE PROCEDURE StoredProcedure1
@oResult int output
AS 
BEGIN
  SET @oResult = 2
END

Upvotes: 0

Related Questions