alexking
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!
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!
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.
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
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
@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
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_STAT US' 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]
The 'sp_UPDATE_TEST2_APPT_STAT
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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
ASKER
Great, thanks - Final version below works a treat!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[sp_UPDATE_TEST2_APP T_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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[sp_UPDATE_TEST2_APP
@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
@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