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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
Ashish PatelCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.