Link to home
Start Free TrialLog in
Avatar of alexking
alexkingFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Conditional SQL query with Return statements

I am new to SQL server 2005 and I need to create a stored procedure to do the following:
I pass a unique reference [MyID] that returns a single record. from the table. This contains the following that are of importance:

[MyID]
[MyCurrentStatus]

I also pass as a parameter @MyNewStatus and @MyOldStatus
What I need to happen is:
If @MyOldStatus = [MyCurrentStatus]
Update [MyCurrentStatus] to be [MyNewStatus] and return 0 to indicate no error

If @MyOldStatus <> [MyCurrentStatus]
Return the value of [MyCurrentStatus]

Any help much appreciated!
Avatar of Ashish Patel
Ashish Patel
Flag of India image

Create Procedure getStatus
@MyId as Int,
@MyNewStatus as Int,
@MyOldStatus As Int
Begin
    Declare @RetVal as int
    Set @RetVal = 0
   
    select @RetVal = [MyCurrentStatus]  from YOURTABLENAME where MyID = @MyID
    If exists (@RetVal = @MyOldStatus )
    Begin
          Update YOURTABLENAME set [MyCurrentStatus] = @MyNewStatus
          Set @RetVal = 0
    End

    Return @RetVal
 
End
Sorry, i forgot to metion to change the tablename i have used in the SP with your table name. Please replae YOURTABLENAME with the one you have.
Avatar of Guy Hengel [angelIII / a3]
what about this:
create procedure dbo.GetMyStatus(@MyID int, @MyNewStatus int , @MyOldStatus int , @result int OUTPUT)
as
begin
   UPDATE yourtable
       SET MyCurrentStatus = @MyNewStatus 
   WHERE MyID = @MyID  
         AND MyCurrentStatus = @MyOldStatus 
   IF @@ROWCOUNT = 0
       SELECT @result = MyCurrentStatus 
          FROM yourtable 
        WHERE MyID = @MyID
   ELSE
      SET @result = 0
end

Open in new window

CREATE PROC YourProcName
@MyID int,
@MyNewStatus int,
@MyOldStatus int
AS
SET NOCOUNT ON
DECLARE @Res int
DECLARE @MyCurrentStatus int
SELECT @MyCurrentStatus =  MyCurrentStatus FROM YourTable WHERE MyID = @MyID
IF @MyCurrentStatus = @MyOldStatus
BEGIN
  UPDATE YourTable SET MyCurrentStatus = @MyNewStatus WHERE MyID = @MyID
  SET @Res = 0
END
ELSE
  SET @Res = @MyCurrentStatus
END
RETURN @Res
GO
Avatar of alexking

ASKER

Many thanks for the fast replies - I tried 2 of the above, both of which return 0 correctly if my field is updated but if the field is not updated the 1st example I give still returns 0 and the second gives the error:

The 'sp_UPDATE_TEST2_APPT_STATUS' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Did I miss something? there is definitely a value in [Appt_Status]


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER Procedure [dbo].[sp_UPDATE_TEST_APPT_STATUS]
          
	 @Appt_ID [int]
 	,@OldStatus [tinyint]
 	,@NewStatus [tinyint]
AS
BEGIN
   DECLARE @result as int
   UPDATE [APPOINTMENTS]
   SET [Appt_Status] = @NewStatus 
   WHERE [Appt_ID] = @Appt_ID AND [Appt_Status] = @OldStatus 
 
   IF @@ROWCOUNT = 0
       SELECT @result = [Appt_Status]
       FROM [APPOINTMENTS] 
       WHERE [Appt_ID] = @Appt_ID AND [Appt_Status] = @OldStatus 
   ELSE
      SET @result = 0
 
END
 
************** METHOD 2 ******************
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER Procedure [dbo].[sp_UPDATE_TEST2_APPT_STATUS]
          
	 @Appt_ID [int]
 	,@OldStatus [tinyint]
 	,@NewStatus [tinyint]
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Res int
	DECLARE @MyCurrentStatus int
 
	SELECT @MyCurrentStatus = [Appt_Status] FROM [APPOINTMENTS] 		  
	WHERE [Appt_ID] = @Appt_ID AND [Appt_Status] = @OldStatus 
 
	IF @MyCurrentStatus = @OldStatus
	BEGIN
	  UPDATE [APPOINTMENTS] SET [Appt_Status] = @NewStatus WHERE [Appt_ID] = @Appt_ID
	  SET @Res = 0
	END
 
	ELSE
	  SET @Res = @MyCurrentStatus
 
	RETURN @Res
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ashish Patel
Ashish Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you actually use RETURN to put the result into the return status, however, my suggestion was to use a OUTPUT parameter, which is recommended. the RETURN value of a procedure should only be used to indicate if the procedure was successful or not.

if you insist on using the RETURN value anyhow...
create procedure dbo.GetMyStatus(@MyID int, @MyNewStatus int , @MyOldStatus int )
as
begin
 DECLARE @result int 
 
   UPDATE yourtable
       SET MyCurrentStatus = @MyNewStatus 
   WHERE MyID = @MyID  
         AND MyCurrentStatus = @MyOldStatus 
   IF @@ROWCOUNT = 0
       SELECT @result = MyCurrentStatus 
          FROM yourtable 
        WHERE MyID = @MyID
   
   SET @result = COALESCE(@result, 0)
 
   RETURN @result
END

Open in new window

Great, thanks - Final version below works a treat!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[sp_UPDATE_TEST2_APPT_STATUS]
       @Appt_ID [int]
       ,@OldStatus [tinyint]
       ,@NewStatus [tinyint]
AS
BEGIN
      SET NOCOUNT ON
      DECLARE @Result tinyint
      DECLARE @CurrentStatus tinyint
 
      SELECT @CurrentStatus = [Appt_Status] FROM [APPOINTMENTS]              
      WHERE [Appt_ID] = @Appt_ID
 
      IF @CurrentStatus = @OldStatus
      BEGIN
            SET @Result = 0
            UPDATE [APPOINTMENTS]
            SET [Appt_Status] = @NewStatus
            WHERE [Appt_ID] = @Appt_ID  
      END
      ELSE
      BEGIN
        SET @Result = @CurrentStatus
      END
      SELECT @Result
END