Stored procedure does not output result

Question

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

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 ?

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

Any ideas why ?

Could you show the C# code that calls this procedure? The problem is there. — Steve, Oct 08 '14 at 06:49
@Steve: I didnot write any C# code Steve. I just wrote this to learn `Stored Procedures`. This is just a `mssql sp` right? C# just calls this right? — now he who must not be named., Oct 08 '14 at 06:51
@Steve: Yup, i am interested on that too. I have used `Dapper` before. But yet to learn how to call this SP from it. Can you guide me with any skeleton please? I will build upon that — now he who must not be named., Oct 08 '14 at 06:54
@Steve: I assume this `Sp` expects a `out` parameter like the `out` keyword in `C#`. Am I correct ? — now he who must not be named., Oct 08 '14 at 06:56

Steve · Answer 1 · 2014-10-08T07:23:49.957

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.

Thanks a lot. Going to try now ) – now he who must not be named. Oct 08 '14 at 07:02 — now he who must not be named., Oct 08 '14 at 07:02

score 2 · Answer 2 · edited May 23 '17 at 12:11

2

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

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:

Execute stored procedure with an Output parameter?

edited May 23 '17 at 12:11

Community

1
1

answered Oct 08 '14 at 06:51

Arion

31,011
10
70
88

Is there a need for '@ oResult = @ Result output'? Wouldn't 'exec StoredProcedure1 @ Result output;SELECT @ Result' work as well? – artm Oct 08 '14 at 07:19

score 0 · Answer 3 · answered Oct 08 '14 at 06:47

0

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

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

answered Oct 08 '14 at 06:47

Neel

11,625
3
43
61

Thanks. But again, it expects me to compulsorily give a parameter and also it displays no result . It just shows `Command executed successfully` :( – now he who must not be named. Oct 08 '14 at 06:48
you need to update your c# code here @nowhewhomustnotbenamed. – Neel Oct 08 '14 at 06:50
I am yet to do that Neel. I will learn how to write that and update the post. Thank you :) – now he who must not be named. Oct 08 '14 at 06:57

score 0 · Answer 4 · answered Oct 20 '14 at 05:09

0

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

answered Oct 20 '14 at 05:09

Rahul wasan

1

Stored procedure does not output result

4 Answers4