Avatar of Robb Hill
Robb HillFlag for United States of America asked on

Help Modifying a Stored Procedure + SQL Server 2000

I have a stored procedure that is excuted from an old  vb.6.0 application.  I have included the stored procedure below.

Essentially the only thing this  vb.6.0 code does it use some logic to create the (@FromDate) and (@ToDate) parameters.  I am wanting to get rid of this old vb.6.0 code and think that this could be written more effectively within the strored procedure itself and not even pass parameters as this strored procedure is and only ever will be used for this funtion.  I will post the vb.6.0 data code in my next post so that someone can help me figure out how to do this in a stored procedure.  If a stored procedure is not the way..I am going to calling this stored procedure from within a DTS package..so maybe I could configure the DTS package to do this algorithm somehow as another option.

Thanks in advance!!
CREATE PROCEDURE sp_FieldActivity (@FromDate datetime, @ToDate datetime) AS
 
-- Delete any previous data
Delete FAR
 
Exec sp_Open_Disp @FromDate, @ToDate
Exec sp_New_Disp @FromDate, @ToDate
Exec sp_Resolved_Disp @FromDate, @ToDate
Exec sp_Resolved_Grp1_Disp @FromDate, @ToDate
Exec sp_Total_Disp @FromDate, @ToDate
Exec sp_Total_Grp1_Disp @FromDate, @ToDate
Exec sp_Total_Grp1_5Days @FromDate, @ToDate
Exec sp_Total_GrpOther_5Days @FromDate, @ToDate
Exec sp_Closed_PilotInst @FromDate, @ToDate
 
Select * from FAR
GO

Open in new window

Microsoft SQL Server

Avatar of undefined
Last Comment
Robb Hill

8/22/2022 - Mon
Mark Wills

Would seem to be a fairly straight forward process to relocate the parameters as declared variables and then populate them however From and To is being determined...

ie intead of :

CREATE PROCEDURE sp_FieldActivity (@FromDate datetime, @ToDate datetime) AS

try:

CREATE PROCEDURE sp_FieldActivity
AS

Declare @FromDate datetime
Declare @ToDate datetime

set @fromdate = getdate() - 7
set @todate = getdate()

etc etc...
ASKER
Robb Hill

Here is the vb logic...stripped down alot so all we see is the date logic
  If Weekday(Now) <> vbSunday Then
            If Weekday(Now()) = vbMonday Then
                sSP = "Execute clarify..sp_FieldActivity '" & Format(Now.AddDays(-3), "MM/dd/yy") & " 12:00:00AM', '" & Format(Now.AddDays(-3), "MM/dd/yy") & " 11:59:59PM'"
            Else
                sSP = "Execute clarify..sp_FieldActivity '" & Format(Now.AddDays(-1), "MM/dd/yy") & " 12:00:00AM', '" & Format(Now.AddDays(-1), "MM/dd/yy") & " 11:59:59PM'"
            End If
  End If
 
        If Weekday(Now) = vbMonday Then
            sSP = "Execute clarify..sp_FieldActivity '" & Format(Now.AddDays(-7), "MM/dd/yy") & " 12:00:00AM', '" & Format(Now.AddDays(-1), "MM/dd/yy") & " 11:59:59PM'"
        End If
 
        If Weekday(Now) = vbMonday And DatePart(DateInterval.Day, Now) < 8 Then
            sSP = "Execute clarify..sp_FieldActivity '" & Format(Now.AddDays(-14), "MM/") & "1" & Format(Now.AddDays(-14), "/yy") & " 12:00:00AM', '" & Format(Now.AddDays(-14), "MM/") & DateDiff("d", Format(Now.AddDays(-14), "MM/") & "1" & Format(Now.AddDays(-14), "/yy"), Format(Now.AddDays(14), "MM/") & "1" & Format(Now.AddDays(14), "/yy")) & Format(Now.AddDays(-14), "/yy") & " 11:59:59PM'"
        End If
		
        If (Weekday(Now) = vbMonday) And (DatePart(DateInterval.Day, Now) < 8) And ((Month(Now.AddDays(-14)) Mod 3) = 0) Then
            sSP = "Execute clarify..sp_FieldActivity '" & Format(Now.AddDays(-90), "MM/") & "1" & Format(Now.AddDays(-90), "/yy") & " 12:00:00AM', '" & Format(Now.AddDays(-14), "MM/") & DateDiff("d", Format(Now.AddDays(-14), "MM/") & "1" & Format(Now.AddDays(-14), "/yy"), Format(Now.AddDays(14), "MM/") & "1" & Format(Now.AddDays(14), "/yy")) & Format(Now.AddDays(-14), "/yy") & " 11:59:59PM'"
       End If

Open in new window

ASKER
Robb Hill

This stored procedure is a daily report..but as you can see from the vb logic..that depending on what day it is in the month it might calculate a daily, weekly, monthly, or quarterly report.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Wills

Well those date criteria can be readily matched, and then used to set up the from and to within the proc...

as in

if datename(dw,getdate()) = 'Monday'
begin
       set @fromdate = convert(varchar(13),dateadd(dd,-3,getdate()),101) + ' 23:59:59'
       set @todate = convert(varchar(13),dateadd(dd,-1,getdate()),101) + ' 00:00:00'
end

etc...
Mark Wills

OK, so let me see if I can translate that yummy code into T-SQL.

Having read it a bit closer, the challenge is the recurring calls to the SP if it is a monday, so the normal 'Daily' run would be:

if datename(dw,getdate()) = 'Monday'
   begin
     set @fromdate = convert(varchar(10),dateadd(dd,-3,getdate()),101) + ' 00:00:00'
     set @todate = convert(varchar(10),dateadd(dd,-3,getdate()),101) + ' 23:59:50'
   end
else
   begin
     set @fromdate = convert(varchar(10),dateadd(dd,-1,getdate()),101) + ' 00:00:00'
     set @todate = convert(varchar(10),dateadd(dd,-1,getdate()),101) + ' 23:59:50'
   end


and now the choice is to either repeat the exec procedures for weekly, monthly and yearly, or, put a covering SP around that... probably the latter...




ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Robb Hill

yes...you are totally on track
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Robb Hill

wow..that looks good.  I am going to give this a test and will reply back with what happens...Thanks so much