Guest BrizDad Posted December 11, 2008 Share Posted December 11, 2008 On the following stored procedure, I want to ammend it so that it will get events that are from the following year. I can see that it is only currently grabbing them if there are no events schedule in the current year.However, I am getting a bit confused with all the IF ELSE statements...It seems that the statement opens by checking to see if it currently has any event IDs. If not, it then begins a procedure to get them from the db. This is where I am getting confused... I would like to change code so that it gets all events regardless of year or country of origination. (The current code seems heavy handed for what it is attempting to achieve - but what do I know?) set ANSI_NULLS OFFset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[proc_WEB_tblWebEvent_Select]/*reg 11/16/07 Added type UpcomingEvents and code to see if there are remaining events for the year. If not, the procedure will return events for the next year. This will ensure the event list on the home page displays events even if they're scheduled in the following year.*//*reg 02/12/08 For @Type UPCOMINGEVENTS, modified WHERE clause to include international shows as well as US (CountryID = 1).*/ @EID INT = NULL, @Type VARCHAR(256) = NULLASBEGIN SET NOCOUNT ON IF (@EID IS NOT NULL) SELECT * FROM tblWebEvent WHERE EID = @EID ELSE BEGIN IF (@Type IS NULL) SELECT * FROM tblWebEvent ORDER BY StartDate ELSE IF UPPER(@Type) = 'USCURRENT' SELECT * FROM tblWebEvent WHERE CountryID = 1 AND YEAR(StartDate) = YEAR(CURRENT_TIMESTAMP) ORDER BY StartDate ELSE IF UPPER(@Type) = 'UPCOMINGEVENTS' IF EXISTS(SELECT * FROM tblWebEvent WHERE CountryID = 1 AND YEAR(StartDate) = YEAR(CURRENT_TIMESTAMP)) SELECT * FROM tblWebEvent WHERE /*CountryID = 1 AND */YEAR(StartDate) = YEAR(CURRENT_TIMESTAMP) ORDER BY StartDate ELSE SELECT * FROM tblWebEvent WHERE /*CountryID = 1 AND */YEAR(StartDate) > YEAR(CURRENT_TIMESTAMP) ORDER BY StartDate ELSE IF UPPER(@Type) = 'USFUTURE' SELECT * FROM tblWebEvent WHERE CountryID = 1 AND YEAR(StartDate) > YEAR(CURRENT_TIMESTAMP) ORDER BY StartDate ELSE IF UPPER(@Type) = 'INTLCURRENT' SELECT * FROM tblWebEvent WHERE CountryID > 1 AND YEAR(StartDate) = YEAR(CURRENT_TIMESTAMP) ORDER BY StartDate ELSE IF UPPER(@Type) = 'INTLFUTURE' SELECT * FROM tblWebEvent WHERE CountryID > 1 AND YEAR(StartDate) > YEAR(CURRENT_TIMESTAMP) ORDER BY StartDate ELSE BEGIN DECLARE @ProcName NVARCHAR(128) SET @ProcName = OBJECT_NAME(@@PROCID) RAISERROR('Error executing %s: Invalid value for parameter @Type: %s.', 16, 1, @Procname, @Type) END ENDEND Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.