Return value in stored proceedure

Question

I am working in asp.net and SQL Server 2005.

I want to know how to return a value from stored procedure or how can we know if a query has been successfully executed in a stored procedure. For example I am inserting some data into a SQL Server database, I want to check if it is inserted correctly or not by using a return value.Here is the code

stored procedure..

  ALTER PROCEDURE [dbo].[practice]
-- Add the parameters for the stored procedure here

@thename varchar(50),
@thedate datetime,

   AS
  BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into tblpractice (name,date) values (@thename,@thedate)

END

codebehind..

     string name=txtname.Text.Trim();
     DateTime date = Convert.ToDateTime(txtdate.Text);
     try
     {
        SqlCommand cmd = new SqlCommand("practice", lcon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@thename", name);
        cmd.Parameters.AddWithValue("@thedate", date);
        lcon.Open();
        cmd.ExecuteNonQuery();


     }
     catch
     {
       throw;

     }
     finally
     {
        if (lcon != null)
        {
            lcon.Close();
        }
     }

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

using output parameters you can get values from store procedure. Here is a good example

Stored procedure output parameter asp.net c#

Another example

http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/

edited May 23 '17 at 12:12

Community

1
1

answered Jan 04 '13 at 11:11

शेखर

17,412
13
61
117

score 1 · Answer 2 · answered Jan 04 '13 at 11:14

Well, simmilar to C#/java sql server can have try catch blocks here is the link:

http://msdn.microsoft.com/en-us/library/ms175976.aspx

You can preform error handling (oracle have this better solved), or you can relativly easy do IF block to solve your problem with a procedure. F.E. if you return some number (that have to be >0) you can preform check on it, and return -1 or what ever if your select within procedure didnt return any entry.

Those are most common ways to solve your kind of problems.

In your case, you can query DB once more for an entry with values you allready have in procedure and return 0/1 if select count(*) FROM table where .... returns value

Philip Wade · Accepted Answer · 2013-01-04T12:56:11.887

ExecuteNonQuery returns an int which is the number of rows affected by the query. In your example, if the stored proc executes successfully, one record will be inserted, so ExecuteNonQuery should return 1.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

Also you should use using when you're dealing with IDisposable objects e.g.

using (var lcon = new SqlConnection(connectionString))
{
    using (var cmd = new SqlCommand("practice", lcon))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@thename", name);
        cmd.Parameters.AddWithValue("@thedate", date);
        lcon.Open();
        return cmd.ExecuteNonQuery();
    }
}

This is the equivalent of try catch finally block i.e. it will close your connection whether there is an error or not.

Return value in stored proceedure

3 Answers3