Executing Stored procedure with prior day filter

We have a reporting system that imports data from our ERP system by way of a stored procedure. The reporting system runs an EXEC StoredProcedureName with a start and end date that is manually adjusted every day. What our end goal would like to be is to have a predefined filter for the previous day so that it will only import the prior day activity. Below is an example of what our execute sql statement looks like.

EXEC dbo.ProphixSalesExport @Start_Date = N'11/20/12', @End_Date = N'11/20/12'

Any insight would be appreciated


more ▼

asked Nov 20, 2012 at 08:10 PM in Default

avatar image

70 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Is your start and end date always the same day?

You could always declare a variable, set the variable to the previous day using getdate ()-1 and use the variable in your sproc.

declare @date date
set @date = getdate () -1

more ▼

answered Nov 20, 2012 at 08:20 PM

avatar image

40.9k 39 95 168

the start and end date is usually the same day except for Mondays when we need to include saturday and sunday activity

Nov 20, 2012 at 08:22 PM tstagliano

Then you could wrap the logic into an if else statement with if day of week = Monday then set @startdate = getdate ()-3 and @enddate = get date () -1 else set @startdate = getdate ()-1 and @enddate = getdate () -1. Then pass those values to your stored procedure.

You could declare a string for your sql statement

declare @sql nvarchar (300)
declare @startdate varchar(8)
declare @enddate varchar(8)

set @startdate = convert(varchar(8), getdate()-1,1)
set @enddate = convert(varchar(8),getdate()-1,1)

set @sql = 'EXEC dbo.[ProphixSalesExport] ' +@startdate + ', ' + @enddate +''

You can wrap this in the if else for your Monday scenario.

Nov 20, 2012 at 10:10 PM Tim

,I understand the string statement but i am having trouble with is understanding how to wrap that in the if else statement for the Monday scenario. Thanks again for your guidance.

Nov 21, 2012 at 01:50 PM tstagliano

select datename(dw, current_timestamp)

That gives you the day of week. If you pass in a particular date from a variable, you could do an if check:

declare @DayOfWeek varchar(15); select @DayOfWeek = datename(dw, '2913-06-13');

if @DayOfWeek = 'Monday' print 'It''s Monday!' else print 'It''s not Monday!'

Nov 21, 2012 at 02:11 PM Kevin Feasel

Tim, You statement worked for one day worth of data, but the next day this process ran, it took that same number and imported in. For example, on the 19th, the wuery returned 9million units and on the 20th there was actually 7million units but the query still added 9million to the report and so on for the next 2 weeks.

Any other suggestions?

Nov 29, 2012 at 04:00 PM tstagliano
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 20, 2012 at 08:10 PM

Seen: 1849 times

Last Updated: Nov 30, 2012 at 02:32 AM

Copyright 2018 Redgate Software. Privacy Policy