Return values from stored procedures in C#

Question

I've seen a few similar questions to this one, but none that were quite the same. Anyhow, I have a stored procedure that ends with the lines,

select SCOPE_IDENTITY()
return

I then get the value by calling executeScalar(). This works perfectly. However, I feel like it would be more correct to just end with return select SCOPE_IDENTITY() (and not to create a new parameter for this return value, either). Is this possible?

Answer 1

There are three ways, in general, to do this... You can do what you're doing - with a Select - in effect returning a single row, single column resultset and letting ADO.Net pull the value out for you. Or

you can use the return statement to return the value, (This approach usurps the common usage pattern for return values of returning an error code) or

you can use what is called an OutPut parameter, (effectively passing the stored proc a reference to a parameter that it (the proc) can populate with a value which will then be available to the ADO.net calling code after the proc returns...

each has advanteges and dis-ad... I like the first option the best cause it's the simplest and easiest to code to... (it's not the most perfomant)

example of using output parameter in stored proc...

  Create Procedure MyProc
  @Name varchar(20),
  @DOB DateTime,
  @EmployeeId Integer Output = Null
  As
  Set NoCount On

     If @EmployeeId Is Null 
       Begin
          Insert Employees(Name, DateofBirth)
          Values (@Name, @DOB)
          Set @EmployeeId = Scope_Identity()
       End
     Else If Exists(Select * From Employees
                    Where EmployeeId =@EmployeeId)
       Begin
           Update Employees Set
              Name = Isnull(@Name, Name),
              DateOfBirth = IsNull(@DOB, DateOfBirth)
           Where EmployeeId = @EmployeeId
       End
     Else
        Raiserror('EmployeeId %d is missing or has been deleted.',
                   16, 1, @EmployeeId)

     Return 0

When you call the stored proc from ADO.Net, and add this parameter to the parameter collection... there is an overload that takes a method parameter which is an enum called ParameterDirection that can take values of ParameterDirection.InputOutput or ParameterDirection.Output, (among others)