CODEWITHSUNDEEP
functiondatetimestored-proceduressql-server-2014ssrs-2012
Jessie P.
Jessie P.

Reputation: 1

Pass function value to stored procedure in SSRS

I have a function that calculate time difference excluding weekends, and I have created a stored procedure to call the function. When I tested the sp, only showed message part, I am new and not sure if this is correct. And I have trouble to pass the parameters to SSRS. Please help, many thanks. 

Alter Proc Test_1 
@DownTime datetime,
@Uptime   datetime =null  --since there are some null value in uptime column
AS  
Begin

print 'enter'

declare @duration int 
select  @duration = [dbo].[CalcWorkMinutes](@DownTime,@Uptime)
print 'abc'

;with cte_test2
as (
SELECT DownTime
,UpTime
,datediff(hh,DownTime,UpTime)  AS duration
FROM Test
)

select DownTime,UpTime,duration from cte_test2

where  DownTime = @DownTime and UpTime=@UpTime 
and duration=@duration
--print 'pass'

print 'duration'+' '+convert(varchar,@duration)
RETURN 

END 
 --execute [dbo].[test_1] @downtime ='2017-06-02 09:00:00.000', @Uptime ='2017-06-05 09:00:00.000'

Upvotes: 0

Views: 79

Answers (1)

Jesse
Jesse

Reputation: 873

Hmm... There's a lot going on in this one. It looks like you are trying to see what downtime was like across a certain timeframe someone will pass in? If that is the case, I'd change the parameters to be: StartDate and EndDate. You don't really need a CTE to apply the parameters... Code below... I am just taking a guess here though:

    Alter Proc Test_1 
@startdate datetime,
@enddate   datetime   --since there are some null value in uptime column
AS  
Begin

print 'enter'



SELECT 
    DownTime
    ,UpTime
    ,[dbo].[CalcWorkMinutes](DownTime,UpTime)  AS duration
FROM Test as a
where 
    downtime >= @startdate
    and b.downtime < dateadd(day,1,@enddate) 


RETURN 

END

Upvotes: 1

Related Questions