Trouble getting max(date) returned from a stored procedure

[kramerd1506]

Hello all, I have a small problem I hope you all can help with. It seems pretty simple, but the resources I can find online don't help .

I'm trying to create a stored procedure in SQL Server 2000 which takes one input parameter as a unique key and returns the max(date_field) associated with that key out of one table.

This is the create procedure text:

CREATE PROCEDURE
lawprod.EB_MAX_CHECK_DATE (
@employeeNumber varchar(20)
)
as
BEGIN
return
(
select max(CHECK_DATE)
from lawprod.PAYMASTR
where EMPLOYEE = @employeeNumber
)
END
GO

When I execute this procedure with a valid input parameter, I get this error:
Syntax error converting the varchar value '05/31/2005' to a column of data type int.

05/31/2005 is the correct response, but the procedure will not return it. However, if I run the procedure text in the query analyzer, it works fine. Can someone explain what's going on here? I'm not asking that anything be converted to int, just return the datetime as it is.

 

[JayKusch]

Get rid of the return ...

CREATE PROCEDURE
lawprod.EB_MAX_CHECK_DATE (
@employeeNumber varchar(20)
)
as
BEGIN
(
select max(CHECK_DATE)
from lawprod.PAYMASTR
where EMPLOYEE = @employeeNumber
)
END
GO



Thanks

J. Kusch

 

[gmmastros]

Return values are integer, and you're returning a date, hence the error message.

Try this procedure instead.

CREATE PROCEDURE 
lawprod.EB_MAX_CHECK_DATE (
@employeeNumber varchar(20)
) 
as
SET NOCOUNT ON

select max(CHECK_DATE) As MaxCheckDate
from lawprod.PAYMASTR 
where EMPLOYEE = @employeeNumber
GO

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JamesLean]

You can only RETURN an integer value. You need to use an output parameter:

CREATE PROCEDURE lawprod.EB_MAX_CHECK_DATE
  @employeeNumber varchar(20),
  @maxdate datetime OUTPUT
AS

SELECT @maxdate = MAX(check_date)
FROM lawprod.PAYMASTR
WHERE EMPLOYEE = @employeeNumber
GO

EXEC lawprod.EB_MAX_CHECK_DATE '123', @d OUTPUT

PRINT @d

--James

 

[kramerd1506]

Thank you all. I knew it had to be so simple it was embarrassing. I tried everything I could think of, and just never thought that you didn't have to "return" anything. Thanks!

 

[JayKusch]

So which one did you go with?

Thanks

J. Kusch

 

[kramerd1506]

I haven't decided yet. I just got the correct answer returned by removing the "return", happily!. I'll see about making it cleaner and chosing a final option soon. I still need to strip the time off of the response.

 

[gmmastros]

To remove the time component of the date time field...

CREATE PROCEDURE 
lawprod.EB_MAX_CHECK_DATE (
@employeeNumber varchar(20)
) 
as
SET NOCOUNT ON

select Convert(VarChar(10), max(CHECK_DATE), 101) As MaxCheckDate
from lawprod.PAYMASTR 
where EMPLOYEE = @employeeNumber
GO

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom