How can I get the return value by stored procedure?

Question

How can i get the return value by stored procedure??????????

Rodrigo Bereta · Answer

Hi Sandeep, I create this procedure based on database AdventureWorks. CREATE PROCEDURE [dbo].[insEmployee] ( @NationalIDNumber varchar(15), @ContactID int, @LoginID varchar(256), @ManagerID int, @Title varchar(50), @BirthDate datetime, @MaritalStatus char(1), @Gender char(1), @HireDate datetime, @SalariedFlag bit, @VacationHours smallint, @SickLeaveHours smallint, @CurrentFlag bit, @rowguid uniqueidentifier, @ModifiedDate datetime, @EmployeeID int OUTPUT ) AS declare @AffectedRows int INSERT HumanResources.Employee ( NationalIDNumber, ContactID, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate ) VALUES( @NationalIDNumber, @ContactID, @LoginID, @ManagerID, @Title, @BirthDate, @MaritalStatus, @Gender, @HireDate, @SalariedFlag, @VacationHours, @SickLeaveHours, @CurrentFlag, @rowguid, @ModifiedDate ) SET @AffectedRowS = @@ROWCOUNT SET @EmployeeID = @@IDENTITY RETURN @AffectedRowS There is two return, one is tradicional return of the procedure with the return command and the other is an output parameter with the new code returned by the identity field. Below is the code to call the procedure with the entity framework. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.EntityClient; using System.Configuration; using System.Data.SqlClient; namespace ConsoleApplication2 { class Program { public static void Main() { try { using (EntityConnection entityConnection = new EntityConnection("name=AdventureWorksEntities")) { entityConnection.Open(); string stringCommand = "AdventureWorksEntities.insEmployee"; EntityCommand entityCommand = new EntityCommand(stringCommand, entityConnection); entityCommand.CommandType = CommandType.StoredProcedure; EntityParameter paramNationalIDNumber = new EntityParameter(); paramNationalIDNumber.ParameterName = "NationalIDNumber"; paramNationalIDNumber.Value = 14417819; entityCommand.Parameters.Add(paramNationalIDNumber); EntityParameter paramContactID = new EntityParameter(); paramContactID.ParameterName = "ContactID"; paramContactID.Value = 1209; entityCommand.Parameters.Add(paramContactID); EntityParameter paramLoginID = new EntityParameter(); paramLoginID.ParameterName = "LoginID"; paramLoginID.Value = @"adventure-works\guy13"; entityCommand.Parameters.Add(paramLoginID); EntityParameter paramManagerID = new EntityParameter(); paramManagerID.ParameterName = "ManagerID"; paramManagerID.Value = 16; entityCommand.Parameters.Add(paramManagerID); EntityParameter paramTitle = new EntityParameter(); paramTitle.ParameterName = "Title"; paramTitle.Value = "Production Technician - WC60 NEW"; entityCommand.Parameters.Add(paramTitle); EntityParameter paramBirthDate = new EntityParameter(); paramBirthDate.ParameterName = "BirthDate"; paramBirthDate.Value = new DateTime(1972, 5, 15, 0, 0, 0); entityCommand.Parameters.Add(paramBirthDate); EntityParameter paramMaritalStatus = new EntityParameter(); paramMaritalStatus.ParameterName = "MaritalStatus"; paramMaritalStatus.Value = "M"; entityCommand.Parameters.Add(paramMaritalStatus); EntityParameter paramGender = new EntityParameter(); paramGender.ParameterName = "Gender"; paramGender.Value = "M"; entityCommand.Parameters.Add(paramGender); EntityParameter paramHireDate = new EntityParameter(); paramHireDate.ParameterName = "HireDate"; paramHireDate.Value = DateTime.Now; entityCommand.Parameters.Add(paramHireDate); EntityParameter paramSalariedFlag = new EntityParameter(); paramSalariedFlag.ParameterName = "SalariedFlag"; paramSalariedFlag.Value = false; entityCommand.Parameters.Add(paramSalariedFlag); EntityParameter paramVacationHours = new EntityParameter(); paramVacationHours.ParameterName = "VacationHours"; paramVacationHours.Value = "21"; entityCommand.Parameters.Add(paramVacationHours); EntityParameter paramSickLeaveHours = new EntityParameter(); paramSickLeaveHours.ParameterName = "SickLeaveHours"; paramSickLeaveHours.Value = "30"; entityCommand.Parameters.Add(paramSickLeaveHours); EntityParameter paramCurrentFlag = new EntityParameter(); paramCurrentFlag.ParameterName = "CurrentFlag"; paramCurrentFlag.Value = true; entityCommand.Parameters.Add(paramCurrentFlag); EntityParameter paramrowguid = new EntityParameter(); paramrowguid.ParameterName = "rowguid"; paramrowguid.Value = Guid.NewGuid(); entityCommand.Parameters.Add(paramrowguid); EntityParameter paramModifiedDate = new EntityParameter(); paramModifiedDate.ParameterName = "ModifiedDate"; paramModifiedDate.Value = DateTime.Now; entityCommand.Parameters.Add(paramModifiedDate); EntityParameter paramEmployeeID = new EntityParameter(); paramEmployeeID.ParameterName = "EmployeeID"; paramEmployeeID.Value = 0; entityCommand.Parameters.Add(paramEmployeeID); EntityParameter paramAffectedRows = new EntityParameter(); paramAffectedRows.ParameterName = "AffectedRows"; paramAffectedRows.Direction = ParameterDirection.ReturnValue; entityCommand.Parameters.Add(paramAffectedRows); entityCommand.ExecuteNonQuery(); Int32 newEmployeeID = (Int32)entityCommand.Parameters["EmployeeID"].Value; Int32 affectedRows = (Int32)entityCommand.Parameters["AffectedRows"].Value; Console.WriteLine(String.Format("AffectedRows: {0}", affectedRows)); Console.WriteLine(String.Format("newEmployeeID: {0}", newEmployeeID)); } } catch (Exception ex) { Console.WriteLine(ex.Message.ToString()); if(ex.InnerException != null) Console.WriteLine(ex.InnerException.ToString()); } finally { Console.ReadKey(); } } } } Regards, Bereta

Jignesh Trivedi · Answer

hi, Please refer http://msdn.microsoft.com/en-us/library/37hwc7kt%28v=vs.80%29.aspx http://geekswithblogs.net/bullpit/archive/2009/01/02/how-to-get-a-return-value-from-a-stored-procedure.aspx hope this will help you.

Satyapriya Nayak · Answer

http://www.gunhansancar.com/blog/how-to-get-return-value-from-stored-procedure-in-sql/

Suthish Nair · Answer

CREATE PROC PROC1 @OUTVALUE INT OUTPUT AS SELECT COUNT(1) INTO Table1 END DECLARE @OUTTest int EXEC PROC1 @OUTVALUE=@OUTTest OUTPUT

How can I get the return value by stored procedure?

Answers (4)