renee35
MIS
- Jan 30, 2007
- 199
What am I doing wrong in the below codeto generate the error below. I added the open and close parentheses, but that didn't fix it:
Incorrect syntax near the keyword 'if'
Create PROCEDURE WeekEnding
(
@endDate datetime,
@weekEndingDate datetime OUTPUT,
@WorkWeek int OUTPUT
)
AS
if @endDate IS NULL
BEGIN
select @endDate = max(EndDate) from ss_hcssca2.dbo.sspayperiods where enddate < getdate()
END
--if they provided a date that isn't a week ending date, go back to the previous week ending (We don't want to partially summarize a week)
--NOTE: This will match if they provided a proper date, leaving @EndDate unchanged
SELECT @weekEndingDate = MAX(EndDate) from ss_hcssca2.dbo.sspayperiods where EndDate <= @EndDate and enddate <= dateadd(dd, -9, getdate())
SELECT @WorkWeek = Period from sspayperiods where enddate = @weekEndingDate
UNION
if @endDate IS NULL
BEGIN
select @endDate = max(EndDate) from ss_hcsscsa.dbo.sspayperiods where enddate < getdate()
END
--if they provided a date that isn't a week ending date, go back to the previous week ending (We don't want to partially summarize a week)
--NOTE: This will match if they provided a proper date, leaving @EndDate unchanged
SELECT @weekEndingDate = MAX(EndDate) from ss_hcsscsa.dbo.sspayperiods where EndDate <= @EndDate and enddate <= dateadd(dd, -9, getdate())
SELECT @WorkWeek = Period from sspayperiods where enddate = @weekEndingDate
UNION
if @endDate IS NULL
BEGIN
select @endDate = max(EndDate) from ss_htrnj.dbo.sspayperiods where enddate < getdate()
END
--if they provided a date that isn't a week ending date, go back to the previous week ending (We don't want to partially summarize a week)
--NOTE: This will match if they provided a proper date, leaving @EndDate unchanged
SELECT @weekEndingDate = MAX(EndDate) from ss_htrnj.dbo.sspayperiods where EndDate <= @EndDate and enddate <= dateadd(dd, -9, getdate())
SELECT @WorkWeek = Period from sspayperiods where enddate = @weekEndingDate
Reason I am writing it this way is because the stored procedure needs to be deployed across all 3 databases and ran in one central database (that's the way the db is set up).
Thanks!
Thanks a bunch!!
-T
Incorrect syntax near the keyword 'if'
Create PROCEDURE WeekEnding
(
@endDate datetime,
@weekEndingDate datetime OUTPUT,
@WorkWeek int OUTPUT
)
AS
if @endDate IS NULL
BEGIN
select @endDate = max(EndDate) from ss_hcssca2.dbo.sspayperiods where enddate < getdate()
END
--if they provided a date that isn't a week ending date, go back to the previous week ending (We don't want to partially summarize a week)
--NOTE: This will match if they provided a proper date, leaving @EndDate unchanged
SELECT @weekEndingDate = MAX(EndDate) from ss_hcssca2.dbo.sspayperiods where EndDate <= @EndDate and enddate <= dateadd(dd, -9, getdate())
SELECT @WorkWeek = Period from sspayperiods where enddate = @weekEndingDate
UNION
if @endDate IS NULL
BEGIN
select @endDate = max(EndDate) from ss_hcsscsa.dbo.sspayperiods where enddate < getdate()
END
--if they provided a date that isn't a week ending date, go back to the previous week ending (We don't want to partially summarize a week)
--NOTE: This will match if they provided a proper date, leaving @EndDate unchanged
SELECT @weekEndingDate = MAX(EndDate) from ss_hcsscsa.dbo.sspayperiods where EndDate <= @EndDate and enddate <= dateadd(dd, -9, getdate())
SELECT @WorkWeek = Period from sspayperiods where enddate = @weekEndingDate
UNION
if @endDate IS NULL
BEGIN
select @endDate = max(EndDate) from ss_htrnj.dbo.sspayperiods where enddate < getdate()
END
--if they provided a date that isn't a week ending date, go back to the previous week ending (We don't want to partially summarize a week)
--NOTE: This will match if they provided a proper date, leaving @EndDate unchanged
SELECT @weekEndingDate = MAX(EndDate) from ss_htrnj.dbo.sspayperiods where EndDate <= @EndDate and enddate <= dateadd(dd, -9, getdate())
SELECT @WorkWeek = Period from sspayperiods where enddate = @weekEndingDate
Reason I am writing it this way is because the stored procedure needs to be deployed across all 3 databases and ran in one central database (that's the way the db is set up).
Thanks!
Thanks a bunch!!
-T