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!
alexkingAsked:
Who is Participating?
 
Ashish PatelConnect With a Mentor Commented:
Now try this

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 tinyint
	DECLARE @MyCurrentStatus tinyint
 
	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 AND [Appt_Status] = @OldStatus 
	  SET @Res = 0
	END
	ELSE
	  SET @Res = @MyCurrentStatus
 
	RETURN @Res
END

Open in new window

0
 
Ashish PatelCommented:
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
0
 
Ashish PatelCommented:
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.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
brejkCommented:
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
0
 
alexkingAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
alexkingAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.