How to check stored procedure return value elegantly

Question

Hello everyone,

Here is my current implementation of a stored procedure which returns Order status for a given Order ID. There are two situations,

1. there is matched Order ID and I will retrieve the related status,
2. there is no matched Order ID (i.e. non-existing Order ID).

My confusion is, how to implement the two functions elegantly/efficiently in one stored procedure, so that I return matched Order ID for situation 1 and also indicate client no matched Order ID in situation 2?

I am using VSTS 2008 + C# + ADO.Net + .Net 3.5 as client, and using SQL Server 2008 as server.

CREATE PROCEDURE [dbo].[GetStatus] 
    @ID [nvarchar](256),
    @Status [int] output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON; 

    -- Insert statements for procedure here 
    SELECT @Status = [Status] 
    FROM [dbo].[OrderStatus]  
    WHERE (@ID = [ID]);
END

thanks in advance, George

Answer 1

There are multiple approaches you can take:

1. Keep everything as is and in your .NET code, if the @status value returned is DBNull, then it will indicate situation 2, otherwise situation 1.

2. Add a RETURN statement to the SPROC and use

   Dim returnValue As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

   returnValue.Direction = ParameterDirection.ReturnValue

   Cmd.Parameters.Add(returnValue)

   in your .NET code to explicitly identify what the SPROC returned and take action accordingly.

As an additional tip, use a SET instead of SELECT when assigning the value to @Status variable in the SPROC. This will guarantee that you get a NULL back if there is no match found. So,

` -- Insert statements for procedure here

SET @Status = SELECT [Status] 
FROM [dbo].[OrderStatus]            
WHERE (@ID = [ID]);`

Answer 2

why are you using output parameter.

you just need to take your stored procedure result in dataset of the data access layer. just check that if (dataset != null) then take value else return appropriate message to your business layer.

Answer 3

You can use the "if statements" inside the stored procedures. the web site at bottom gives you some tips.

http://translate.google.com.br/translate?u=http%3A%2F%2Fmail.firebase.com.br%2Fpipermail%2Flista_firebase.com.br%2F2005-November%2F021883.html&sl=pt&tl=en&hl=pt-BR&ie=UTF-8