Can this SQL Statement Work?

[chadau]

I have tried this but it doesn't seem to be working.

ALTER PROCEDURE GetApprover
@Cost money,
@EmployeeNumber varchar(5) = NULL

AS

DECLARE @ManagerCost money
DECLARE @MaxCost money

SET @ManagerCost = (SELECT Cost FROM Approver WHERE EmployeeNum = @EmployeeNumber)

SET @MaxCost = (SELECT TOP 1 Cost FROM Approver WHERE Cost > @ManagerCost ORDER BY Cost DESC)

RETURN @MaxCost

 

[NoCoolHandle]

try
have tried this but it doesn't seem to be working.

ALTER PROCEDURE GetApprover
@Cost money,
@EmployeeNumber varchar(5) = NULL

AS

DECLARE @ManagerCost money
DECLARE @MaxCost money

(SELECT @ManagerCost = Cost FROM Approver WHERE EmployeeNum = @EmployeeNumber)

(SELECT TOP 1 @MaxCost = Cost FROM Approver WHERE Cost > @ManagerCost ORDER BY Cost DESC)

or maybe

ALTER PROCEDURE GetApprover
@Cost money,
@EmployeeNumber varchar(5) = NULL

AS

DECLARE @ManagerCost money
DECLARE @MaxCost money

(SELECT @ManagerCost = Cost FROM Approver WHERE EmployeeNum = @EmployeeNumber)
(SELECT @MaxCost = max(Cost) FROM Approver WHERE Cost > @ManagerCost)

 

[NoCoolHandle]

Or

ALTER PROCEDURE GetApprover
@Cost money,
@EmployeeNumber varchar(5) = NULL

AS

DECLARE @ManagerCost money
DECLARE @MaxCost money
[green]-- Assuming all you really need is the @maxCost parameter[/green]
(SELECT max(Cost) as MaxCost FROM Approver WHERE Cost > (SELECT Cost FROM Approver WHERE EmployeeNum = @EmployeeNumber))

or

ALTER PROCEDURE GetApprover
@Cost money,
@EmployeeNumber varchar(5) = NULL

AS

DECLARE @MaxCost money
[green]-- Assuming all you really need is the @maxCost parameter[/green]
(SELECT @MaxCost = max(Cost)  FROM Approver WHERE Cost > (SELECT Cost FROM Approver WHERE EmployeeNum = @EmployeeNumber))