• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

@RETURN_VALUE ??

Hi,

I have got the following SQL stroerd procedure below. Basically the query should return either a 1 or a 0, which it does, the only problem is that it returns it in a new column called "ReturnStatus", what i need to do is return the integer result in the actual @RETURN_VALUE.

Does anybody know how to do this ?

Thanks

Rob

--------------------------Stored Procedure Below------------------------------------

CREATE PROCEDURE dbo.ValidateReservation
(
     @Computer_name As VarChar(50),
     @Res_start_time As DateTime,
     @Res_end_time As DateTime
)
AS

Declare @ReturnStatus As Int

Select Computer_name,

Max ( Case When ( @Res_start_time >= Session_start_time) And  ( @Res_start_time < Session_end_time ) Or  ( @Res_start_time > Session_start_time ) And ( @Res_start_time < Session_end_time )
Or ( @Res_end_time > Session_start_time ) And ( @Res_end_time <= session_end_time ) Then 1 Else 0 End ) As ReturnStatus

From Session
Where @Computer_name = Computer_name

group by computer_name
GO
0
Rob_2002
Asked:
Rob_2002
  • 2
  • 2
  • 2
1 Solution
 
JimV_ATLCommented:
This should work:


CREATE PROCEDURE dbo.ValidateReservation
(
    @Computer_name As VarChar(50),
    @Res_start_time As DateTime,
    @Res_end_time As DateTime,
    @ReturnStatus As Int Output
)
AS



Select
@ReturnStatus = Max (
     Case
     When
     ( @Res_start_time >= Session_start_time)
     And  ( @Res_start_time < Session_end_time )
     Or  ( @Res_start_time > Session_start_time )
     And ( @Res_start_time < Session_end_time )
     Or ( @Res_end_time > Session_start_time )
     And ( @Res_end_time <= session_end_time )
     Then 1 Else 0 End )

From Session
Where @Computer_name = Computer_name

group by computer_name

**********************************************************

When you go to call the procedure, you'll need to specify output after @ReturnStatus, e.g.

exec dbo.ValidateReservation @Computer_name, @Res_start_time, @Res_end_time, @ReturnStatus Output
)
0
 
Anthony PerkinsCommented:
Make the following change:

CREATE PROCEDURE dbo.ValidateReservation
(
    @Computer_name As VarChar(50),
    @Res_start_time As DateTime,
    @Res_end_time As DateTime
)
AS

Declare @ReturnStatus As Int

Select Computer_name,

Max ( Case When ( @Res_start_time >= Session_start_time) And  ( @Res_start_time < Session_end_time ) Or  ( @Res_start_time > Session_start_time ) And ( @Res_start_time < Session_end_time )
Or ( @Res_end_time > Session_start_time ) And ( @Res_end_time <= session_end_time ) Then 1 Else 0 End ) As ReturnStatus

From Session
Where @Computer_name = Computer_name

group by computer_name

RETURN @ReturnStatus                  -- Add this line

GO

Anthony
0
 
Anthony PerkinsCommented:
Actually my code is not correct, it should be:
CREATE PROCEDURE dbo.ValidateReservation
(
   @Computer_name As VarChar(50),
   @Res_start_time As DateTime,
   @Res_end_time As DateTime
)
AS

Declare @ReturnStatus As Int

Select @ReturnStatus =
Max ( Case When ( @Res_start_time >= Session_start_time) And  ( @Res_start_time < Session_end_time ) Or  ( @Res_start_time > Session_start_time ) And ( @Res_start_time < Session_end_time )
Or ( @Res_end_time > Session_start_time ) And ( @Res_end_time <= session_end_time ) Then 1 Else 0 End )

From Session
Where @Computer_name = Computer_name

group by computer_name

RETURN @ReturnStatus                  -- Add this line

GO
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Rob_2002Author Commented:
Hi,

Thanks for your soloutions, they both work. I'm going to use the one from AcPerkin's because it returns the value into the @RETURN_VALUE, which is what I wanted. I would really like to split the points up, do you know ho to do that? I feel that you both deserve some for your excellence :)

Thanks a lot you are both brilliant !!

Best Wishes Rob
0
 
JimV_ATLCommented:
Thanks for your thoughtfulness.  Typically, in this case, one would post a question with additional (consolation) points with the title "Points for JimV_ATL" or whomever.  It's not necessary.  It's not necessary, though.

0
 
Rob_2002Author Commented:
Hi JimV ATL,

You have convinced me :)

I've posted another question with the subject as you've given. Please accept it and the points are yours

Regards

Rob
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now