The return value of the stored procedure

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 date 0, which is not the desired result from the database).

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 whole, then you should use one of these methods (some of them are explained by previous answers):

• Use SELECT in your procedure

• Use the OUTPUT parameter

• Use a user-defined function instead

Answer 2

There is no need to declare a variable and assign a value to it. Just return the select statement.

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

Although you should be aware that depending on your data this may return more than one value.

EDIT

If you want you can do it like this:

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

And then you can use it like this:

 Declare @res datetime exec get 3, @res print @res 

Answer 3

You must select a value:

 select @OADate 

Your value will be the first value in the first row of the first result set.

Answer 4

See CREATE A PROCEDURE

you need to use the OUTPUT clause

 **OUTPUT** Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. 

Also, returning a single value is similar to calling a CUSTOMIZED USER FUNCTION rather than a STORED PROCEDURE