Execute stored procedure on fixed days only

Question

Execute stored procedure on fixed days only

pwalter83

SSChampion

Points: 14575
More actions
September 27, 2019 at 9:48 am

#3683915

Hi,
I have a requirement to only execute the SP on tuesday and friday. The SP is called within the SSIS package and I have tried to make the change in the SP itself. I am using Netezza so included the following in the SP but I am getting errors when executing the SP.
IF DATE_PART('DOW',CURRENT_DATE) IN ('3','6') -- this is to check if the days of the week are tuesday and friday.
BEGIN
Can someone please give some insights on how to make this work ? Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Looks like Netazza syntax is different from SQL Server syntax. Not sure there's anyone here who will be able to help you - you may be better off on a Netazza forum. The T-SQL statement would look something like this:

IF DATEPART('dw',CURRENT_TIMESTAMP) IN (3,6)...

What error are you getting, incidentally?

John

This reply was modified 5 years, 2 months ago by John Mitchell-245523.

pwalter83 SSChampion Points: 14575 More actions · Answer 2

The Netezza syntax is okay and equivalent of SQL server but I think I am not using it correctly in the SP.

The error I am getting is - ERROR: syntax error, unexpected IF, expecting BEGIN at or near "IF"

Do you know how to use this properly within the SP for this to work ? Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

That doesn't sound like a SQL Server error message. Do you get the same error message if you call the stored procedure from SSMS instead of from the SSIS package? Please post the whole stored procedure definition.

John

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

try this - its just a guess

IF (DATE_PART('DOW',CURRENT_DATE) IN ('3','6'))

begin

(put brackets around your evaluation)

MVDBA

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 5

CREATE PROCEDURE #TestDW 
(
    @CheckDayOfWeek bit = 0
) AS
IF @CheckDayOfWeek = 0 OR DATEPART(dw,GETDATE()) IN (3,6) BEGIN
    PRINT 'Hello'
    /* Rest of SP here */
END
GO
/* Run with date check that today is Tuesday or Friday (3, 6) */
EXEC #TestDW 1
GO
/* Run sp whatever, don't bother to check date */
EXEC #TestDW 0

Phil Parkin SSC Guru Points: 246979 More actions · Answer 6

pwalter83 wrote:

The Netezza syntax is okay and equivalent of SQL server

Based on what you posted, it really isn't.

Or if it is, where did you get your syntax from?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101118 More actions · Answer 7

Based on my reading of Netezza syntax online:

IF DATE_PART('DOW',CURRENT_DATE) IN ('3','6') THEN

statement

[statement] ...

END IF;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".