Return value question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return value question

Author

Topic

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-16 : 17:34:14

Hi I just made the following sp:

CREATE PROCEDURE TotalContracts2
(@returnparameter)
AS
SET @return = ( SELECT COUNT(Contract.ContractNo) AS NumContracts
FROM Contract LEFT JOIN Faculty
ON Contract.Supervisor = Faculty.FacNo)
RETURN @returnparameter

Can anyone tell me how to set it's return status to -1 incase it returns an unknown value such as dept in the faculty table?

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 17:47:58

In the code you set the value of @return, but you don't have @return anywhere. To really return a value with @returnparameter you need to make it an output parameter, and then just set its value but don't put it after the RETURN statement.

To return a -1 status just put
RETURN -1
in the code and use an IF statement to test for the conditions that must return -1. I can't tell you what that would look like because I have no idea what "unknown value such as dept in the faculty table" means in your data.

Thing to see here is, use RETURN to return a status value and it has to be an integer. Don't use RETURN to return data, use either output parameters, or SELECT.

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-16 : 17:56:37

Thanks, using your suggestion ive done this:

CREATE PROCEDURE TotalContracts2
(@return output)
AS
SET @return = ( SELECT COUNT(Contract.ContractNo) AS NumContracts
FROM Contract LEFT JOIN Faculty
ON Contract.Supervisor = Faculty.FacNo)
RETURN -1

Department is a feild in the Contract table but I don't want it as part of the result set.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 18:06:56

OK, so then something like this


CREATE PROCEDURE TotalContracts2
(@return output)
AS
SELECT @return = COUNT(Contract.ContractNo)
FROM Contract 
LEFT JOIN Faculty
ON Contract.Supervisor = Faculty.FacNo
IF something to do with department field
    RETURN -1
ELSE
    RETURN 0

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-16 : 18:08:47

I would do something like this but rather get a 2nd opinion:

CREATE PROCEDURE TotalContracts2
@returnparameter int output,
@return varchar(20)
AS

-- in my opinion this below select statement will give the same result --as
--SELECT COUNT(Contract.ContractNo) AS NumContracts FROM Contract

SELECT @return = ( SELECT COUNT(Contract.ContractNo) AS NumContracts
FROM Contract LEFT JOIN Faculty
ON Contract.Supervisor = Faculty.FacNo)

IF @return in ( unknown values )
SET @returnparameter = -1
ELSE --known values
SET @returnparameter = 0

--******************************************************
--then you would have in your calling procedure

DECLARE @returnVal as int

EXEC totalcontracts2
@returnparameter = @returnVal output,

IF @returnval = -1
BEGIN
----
--- error code here
END
Else
BEGIN
---
---normal code here
END
go

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-16 : 18:27:39

Thanks for both of these. It's great to get these variations.
I very much appreciate your experience.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-16 : 18:34:36

Just for my interest sake nomad please tell me if these two queries retrn the same count please

SELECT COUNT(Contract.ContractNo) AS NumContracts FROM Contract

SELECT COUNT(Contract.ContractNo) AS NumContracts
FROM Contract LEFT JOIN Faculty
ON Contract.Supervisor = Faculty.FacNo

cheers ! :)

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-18 : 19:44:53

Sorry Jim, I am just now getting back to this post been busy and I will try thsi

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-18 : 19:48:43

Yes they gave the same results

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources