Jump to content

Newbie Help - Ammend Sql


Guest BrizDad
 Share

Recommended Posts

Guest BrizDad

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...