CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
none
none

Reputation: 4847

stored procedure returning value

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?

Upvotes: 4

Views: 18901

Answers (4)

codingbadger
codingbadger

Reputation: 44042

There isn't any 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 to you could do it this way:

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 so:

Declare @res datetime

exec get 3, @res

print @res

Upvotes: 7

RBarryYoung
RBarryYoung

Reputation: 56785

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

Upvotes: 9

Jamiec
Jamiec

Reputation: 136239

You should select the value:

select @OADate

Your value will be the first value in the first row on the first resultset.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166616

Have a look at CREATE 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 seems like it is calling for a USER DEFINED SCALAR FUNCTION, rather than a STORED PROCEDURE

Upvotes: 0

Related Questions