stored procedure returning value

Question

using this code :

ALTER PROCEDURE [dbo].[get](@i int)
AS
BEGIN
    declare @ADate datetime
    select @ADate = ADate 
    from table
    where i=@i
    and DateDiff(day ,getDate(), aDate  ) > 0
    and aDate is not null
    order by aDate asc
    return select @ADAte   
END

this returns 0 (or system 0 date time, which is not the desired result from the data base).

execute code

Declare @res datetime

exec @res = get 3

print @res

why?

Answer 1

Stored Procedures in SQL Server can only RETURN integers. If you need to return anything other than a single integer, then you should use one of these methods (some explained by the previous answers):

• Use a SELECT in your procedure

• Use an OUTPUT Parameter

• Use a User Defined Function instead