Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure Error

Status
Not open for further replies.

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top