Get Return Value from Stored procedure in asp.net

Question

i have a stored procedure

ALTER PROC TESTLOGIN
    @UserName varchar(50),
    @password varchar(50)
As
Begin
    declare @return int;

    set @return  = (SELECT COUNT(*) 
                    FROM CPUser 
                    WHERE UserName = @UserName 
                    AND Password = @password);

    return @return;
End

and in c#

SqlConnection con = db.con;
SqlCommand cmd = new SqlCommand("TESTLOGIN", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parm = new SqlParameter("@return", SqlDbType.Int);
parm.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(parm);
cmd.Parameters.Add(new SqlParameter("@UserName", txtUserName.Text.ToString().Trim()));
cmd.Parameters.Add(new SqlParameter("@password", txtPassword.Text.ToString().Trim()));

cmd.ExecuteNonQuery();
con.Close();

int id = Convert.ToInt32(parm.Value);

but it always return 0. Please help me to solve this problem

@adt if directly execute the procedure in SQL Server, it works correctly but in c#, it always gives 0 — Ahmad Abbasi, Feb 11 '13 at 10:29
possible duplicate of [Getting return value from stored procedure in C#](http://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c-sharp) — Chris Diver, Feb 11 '13 at 10:40

score 41 · Accepted Answer · answered Feb 11 '13 at 16:15

41

You need a parameter with Direction set to ParameterDirection.ReturnValue in code but no need to add an extra parameter in SP. Try this

  SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
  returnParameter.Direction = ParameterDirection.ReturnValue;
  cmd.ExecuteNonQuery();

  int id = (int) returnParameter.Value;

answered Feb 11 '13 at 16:15

Shafeeq Koorimannil

724
5
14

thanks, running fine. (Notice than at this time `Parameters.Add` is deprecated) – dani herrera Oct 10 '13 at 13:29
1

`Parameters.Add(string parameterName, object value)` is deprecated, but `Parameters.Add(string parameterName, SqlDbType sqlDbType)` isn't. – j.i.h. Nov 25 '13 at 13:46
I am returning string value but its giving an exeption `Conversion failed when converting the varchar value 'my returned value' to data type int.` – SMI Mar 21 '14 at 07:39
1

please check whether you are returning the value as varchar from SP. It has to be int value. If you want to return string then it should be SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.String); – Shafeeq Koorimannil Mar 21 '14 at 13:17
What about one line code: `cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;` – Zeeshanef Jun 16 '14 at 15:55

score 2 · Answer 2 · answered Mar 16 '14 at 03:26

2 things.

The query has to complete on sql server before the return value is sent.
The results have to be captured and then finish executing before the return value gets to the object.

In English, finish the work and then retrieve the value.

this will not work:

                cmm.ExecuteReader();
                int i = (int) cmm.Parameters["@RETURN_VALUE"].Value;

This will work:

                        SqlDataReader reader = cmm.ExecuteReader();
                        reader.Close();

                        foreach (SqlParameter prm in cmd.Parameters)
                        {
                           Debug.WriteLine("");
                           Debug.WriteLine("Name " + prm.ParameterName);
                           Debug.WriteLine("Type " + prm.SqlDbType.ToString());
                           Debug.WriteLine("Size " + prm.Size.ToString());
                           Debug.WriteLine("Direction " + prm.Direction.ToString());
                           Debug.WriteLine("Value " + prm.Value);

                        }

if you are not sure check the value of the parameter before during and after the results have been processed by the reader.

score 1 · Answer 3 · answered Feb 11 '13 at 10:35

you can try this.Add the parameter as output direction and after executing the query get the output parameter value.

  SqlParameter parmOUT = new SqlParameter("@return", SqlDbType.Int);
  parmOUT.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(parmOUT);
  cmd.ExecuteNonQuery();
  int returnVALUE = (int)cmd.Parameters["@return"].Value;

score 0 · Answer 4 · answered Feb 11 '13 at 13:52

0

Procedure never returns a value.You have to use a output parameter in store procedure.

ALTER PROC TESTLOGIN
@UserName   varchar(50),
@password   varchar(50)
@retvalue int output
 as
 Begin
    declare @return     int 
    set @return  = (Select COUNT(*) 
    FROM    CPUser  
    WHERE   UserName = @UserName AND Password = @password)

   set @retvalue=@return
  End

Then you have to add a sqlparameter from c# whose parameter direction is out. Hope this make sense.

answered Feb 11 '13 at 13:52

user1990587

386
4
15

8

You are mistaken, a stored procedure in SQL Server *always* returns a value, which is always an `int` value too. You may have never retrieved the return value in your queries, but that doesn't mean that an SP doesn't have one. Please take a(nother) look at the [RETURN manual](http://msdn.microsoft.com/en-us/library/ms174998.aspx "RETURN (Transact-SQL)"). – Andriy M Feb 11 '13 at 14:05
I have retrieved the return value in my queries. But i have forget to mention this. Also i have studied at RETURN manual whose link provided by you.Thanks for link and reforming my answer. – user1990587 Feb 11 '13 at 14:29

score -2 · Answer 5 · edited Jul 08 '15 at 13:20

-2

If you want to to know how to return a value from stored procedure to Visual Basic.NET. Please read this tutorial: How to return a value from stored procedure

I used the following stored procedure to return the value.

CREATE PROCEDURE usp_get_count

AS
BEGIN
 DECLARE @VALUE int;

 SET @VALUE=(SELECT COUNT(*) FROM tblCar);

 RETURN @VALUE;

END
GO

edited Jul 08 '15 at 13:20

ase

13,231
4
34
46

answered Mar 16 '14 at 12:13

Saleeq Mohammed

1
2

Welcome to [so]. When answering questions, please make sure to disclose any affiliation with any references or links in the post. Thanks! – Qantas 94 Heavy Mar 16 '14 at 12:31

score -3 · Answer 6 · answered Feb 11 '13 at 10:58

Do it this way (make necessary changes in code)..

            SqlConnection con = new SqlConnection(GetConnectionString());
            con.Open();
            SqlCommand cmd = new SqlCommand("CheckUser", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("username", username.Text);
            SqlParameter p2 = new SqlParameter("password", password.Text);
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            SqlDataReader rd = cmd.ExecuteReader();
            if(rd.HasRows)
            {
                //do the things
            }
            else
            {
                lblinfo.Text = "abc";
            }

This is not a nice alternative since it adds the overhead of the data reader. An output parameter is the most efficient way. — Lukos, Feb 11 '13 at 13:55

Get Return Value from Stored procedure in asp.net

6 Answers6

Linked