Executing a stored procedure

  • So perhaps I'm just having a brain-fart this morning, but I could use another set of eyes...

    I'm in the process of automating a current manual task which involves running a stored procedure then a DTS task and a few other steps...

    The stored procedure needs to be run with last week's dates to pull the appropriate data. It accepts 2 parameters @startDate Datetime and @EndDate Datetime.

    I am able to run the code from SSMS like this.

    DECLARE @StartDate DATETIME, @EndDate DATETIME

    SELECT @StartDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0),

    @EndDate = DateAdd(ms,-3, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

    EXECUTE [myProcedure] @StartDate, @EndDate

    I can also execute it like this, which is basically what the user was doing via the manual process

    EXECUTE [myProcedure]

    @StartDate = '2009-04-13 00:00:00.000',

    @EndDate = '2009-04-19 23:59:59.997'

    Anyone know why I keep seeing an error when I try to execute it like this?

    EXECUTE [myProcedure]

    @StartDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0),

    @EndDate = DateAdd(ms,-3, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

    I keep getting the below as an error.

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'wk'.

    As stated above I can execute this other ways, but I'm just wondering why? Any thoughts?

    Thanks in advance,

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • It doesn't like calculating values within the execute command. Do it like your first example.

    As an aside, I'd change the way you're doing the end date. Make it the exact end date, and make the proc use less than on that instead of between. It's a better practice.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (4/24/2009)


    It doesn't like calculating values within the execute command. Do it like your first example.

    Thanks Gus, not enough coffee this morning, just couldn't figure why it wouldn't work.

    As an aside, I'd change the way you're doing the end date. Make it the exact end date, and make the proc use less than on that instead of between. It's a better practice.

    Yeah I know about the < vs between bit, I'm fixing someone else's code and wanted to recreate the original process to make sure I had the data and logic correct before I started making wholesale changes to improve performance, structure and other such things.

    Thanks again,

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Definitely know how that goes. Change one thing at a time, so if it breaks, you know exactly what to undo.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • That's the plan.

    Thanks again.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply