Stored Procedure within a case statement 1

[collierd2]

Hello

I have a stored procedure (spInventory)
The procedure returns all stock movements between 2 passed parameters (@startdate and @enddate)
Both are smalldatetime

e.g. exec spInventory '2009-01-01', @currentdate
Returns all movements from the start of the year up until now (@currentdate is defined as smalldatetime and equal to getdate())


Now, I want to be able to automate the running of it (sunday to sunday)
The key issue is, I need to also run partial weeks at month end

I can think through the best approach to this (unless anybody has any suggestions)


My main problem is, how do I use a case statement to run it dependant upon whether it's monthend and mid month - I think I have a syntax issue with my case statement


Eventually, I would like to put this in a DTS package and automate it, although I think if I can resolve the main problem, that should be straight forward


My code at the moment is:

declare @sdate smalldatetime
declare @edate smalldatetime
declare @currentdate smalldatetime
declare @startmonth tinyint
declare @endmonth tinyint

set @currentdate = getdate()
set @edate = dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)
set @sdate = dateadd(dd,-6,@edate)
set @startmonth = month(@sdate)
set @endmonth = month(@edate)

case
   when (@startmonth = @endmonth) then
      exec spConsignment @startdate = @sdate, @enddate = @edate
   else exec spConsignment @startdate = @sdate, @enddate = @edate
end

go

Thanks

 

[gmmastros]

A case statement works well for conditional data handling. To do conditional FLOW logic, you need to use IF instead. Like this:

declare @sdate smalldatetime
declare @edate smalldatetime
declare @currentdate smalldatetime
declare @startmonth tinyint
declare @endmonth tinyint

set @currentdate = getdate()
set @edate = dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)
set @sdate = dateadd(dd,-6,@edate)
set @startmonth = month(@sdate)
set @endmonth = month(@edate)

If @startmonth = @endmonth
  Begin
    exec spConsignment @startdate = @sdate, @enddate = @edate
  End
Else 
  Begin
    exec spConsignment @startdate = @sdate, @enddate = @edate
  End

go

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[collierd2]

Fantastic, thanks for your help