CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Anshu Saket
Anshu Saket

Reputation: 21

Temp Procedure Not giving Output

I created a temporary procedure in SQL Server but when I am trying to execute it, it worls fine except for output parameter. Output prarameter is not being able to get the output from SP. I am not sure why this is occuring.

Alter Procedure #AgeDifference
@DOB date,
@output nvarchar(500) Out
AS
Begin
Declare @tempdate date,@year int,@month int,@day int
Set @tempdate=@DOB

Select @year=DateDiff(Year,@tempdate,Getdate())-
   CASE
    WHEN DatePart(Month,@tempdate)>DatePart(Month,Getdate()) 
    OR ( DatePart(Month,@tempdate)=DatePart(Month,Getdate()) AND 
DatePart(Day,@tempdate)>DatePart(Day,Getdate()))
    THEN 1
    Else 0
   END


Set @tempdate=DateAdd(Year,@year,@tempdate)


Select @month=DateDiff(Month,@tempdate,Getdate())-
   CASE
    WHEN DatePart(Day,@tempdate)>DatePart(Day,Getdate()) 
    THEN 1
    Else 0
   END


Set @tempdate=DateAdd(Month,@month,@tempdate)

Select @day=DateDiff(Day,@tempdate,Getdate())

Set @output= 'The difference is '+CAST(@year as nvarchar(10))+ ' Years '+CAST(@month as nvarchar(10))+ ' Months '
  +CAST(@day as nvarchar(10)) +' Days '

print @output
End


Declare @out nvarchar(100)
EXEC #AgeDifference '27/04/1994',@out
print @out

Upvotes: 0

Views: 40

Answers (2)

JERRY
JERRY

Reputation: 1173

Please execute with proper date format. it succeed with below thread for me.
Declare @out nvarchar(100) EXEC #AgeDifference '2018-04-27',@out PRINT @out

Upvotes: 0

DatabaseCoder
DatabaseCoder

Reputation: 2032

Add OUT with output parameter too. Change second last line of your code to -

EXEC #AgeDifference '27/04/1994',@out OUT

Upvotes: 1

Related Questions