asked on

SQL Server IF...ELSE Statements

I've always struggled a little with the Syntax of and how to use IF...ELSE statements in T-SQL. Now I'm in a job which requires much more use of SQL, I'd like to clarify my knowledge!
I have a stored procedure which has a parameter called @LeavingDate with a default value of NULL. If a date is passed, then an additional clause is added to the WHERE statement at the end of the query. So, I use the following syntax:

IF @LeavingDate IS NULL
BEGIN
	SELECT AdmissionNumber, AcademicYear,
	CASE WHEN AcademicYear = 2013 THEN '13' + CAST(EthnicOrigin AS NVARCHAR(2))
		ELSE '12' + CAST(EthnicOrigin AS NVARCHAR(2)) END AS EthnicOrigin
	INTO #TempApplicationEO
	FROM NextStudentPersonalTable
	WHERE AdmissionNumber IS NOT NULL
END
ELSE
	SELECT AdmissionNumber, AcademicYear,
	CASE WHEN AcademicYear = 2013 THEN '13' + CAST(EthnicOrigin AS NVARCHAR(2))
		ELSE '12' + CAST(EthnicOrigin AS NVARCHAR(2)) END AS EthnicOrigin
	INTO #TempApplicationEO
	FROM NextStudentPersonalTable
	WHERE AdmissionNumber IS NOT NULL AND LeavingDate = @LeavingDate

Open in new window

Reading around, I've seen examples where BEGIN/END encloses the first part, but not the ELSE, examples where BEGIN/END is not used at all and examples where it is used in both.
The problem I have with the above is that the code will not parse; I get a message saying that 'There is already an object named '#TempApplicationEO' in the database - it is complaining about the second SELECT INTO following the ELSE. I've tried the code with all variations of BEGIN/END that I've mentioned and all give the same error.
So, that leads me to suspect that both blocks will try and execute (indeed I've seen this behaviour elsewhere in some other code I wrote).
Of course, I could simply use IF @LEavingDate IS NULL followed by IF @LeavingDate IS NOT NULL, but I want to nail how it works!

Patrick Matthews

Strictly speaking, if you execute a single statement you do not need the BEGIN...END. I recommend using it, though, to make your code more readable.

Christopher Gordon

What about this approach?

SELECT AdmissionNumber, AcademicYear,
      CASE WHEN AcademicYear = 2013 THEN '13' + CAST(EthnicOrigin AS NVARCHAR(2))
            ELSE '12' + CAST(EthnicOrigin AS NVARCHAR(2)) END AS EthnicOrigin
      INTO #TempApplicationEO
      FROM NextStudentPersonalTable
      WHERE AdmissionNumber IS NOT NULL and LeavingDate = ISNULL(@LeavingDate, LeavingDate)

The ISNULL() function at the end will use the parameter if it is provided or ignore the parameter if it is NULL.

Karl_mark

ASKER

Clever thinking gohord; hadn't thought of that!
I'll give you some points at the end, but I still need to figure out the IF...ELSE statements as I will need them in the future. How about adding this one which also doesn't seem to work properly:

IF @CompCount IS NULL 
BEGIN
	INSERT INTO ESASTable([Admission Number], [Choice Number], [Comp Code])
	VALUES(@AdNo, 1, @NewChoice)
END
ELSE IF @SecondChoice = 0
	BEGIN
		DELETE ESASTable WHERE [Admission Number] = @AdNo 
                    AND [Choice Number] = 2
		SELECT @Choice1 = [Comp Code] FROM ESASTable
                    WHERE [Admission Number] = @AdNo
		INSERT INTO ESASTable([Admission Number], [Choice Number], [Comp Code])
			VALUES(@AdNo, 2, @Choice1)
		UPDATE ESASTable SET [Comp Code] = @NewChoice, Manual_Edit = 1
                    WHERE [Admission Number] = @AdNo
                    AND [Choice Number] = 1
	END
	ELSE IF @CompCount = 1
		BEGIN
			INSERT INTO ESASTable([Admission Number], [Choice Number], [Comp Code])
				VALUES(@AdNo, 2, @NewChoice)
		END
		ELSE IF @CompCount = 2
			BEGIN
				UPDATE ESASTable SET [Comp Code] = @NewChoice, Manual_Edit = 1 
					WHERE [Admission Number] = @AdNo AND [Choice Number] = 2
			END
END

Open in new window

sachitjain

It's better you create your temp table first and then use it like this

create table #TempApplicationEO (AdmissionNumber int, AcademicYear int, EthnicOrigin varchar(3))
IF @LeavingDate IS NULL
BEGIN
      INSERT INTO #TempApplicationEO (AdmissionNumber, AcademicYear, EthnicOrigin)
      SELECT AdmissionNumber, AcademicYear,
      CASE WHEN AcademicYear = 2013 THEN '13' + CAST(EthnicOrigin AS NVARCHAR(2))
            ELSE '12' + CAST(EthnicOrigin AS NVARCHAR(2)) END AS EthnicOrigin
      FROM NextStudentPersonalTable
      WHERE AdmissionNumber IS NOT NULL
END
ELSE
BEGIN
      INSERT INTO #TempApplicationEO (AdmissionNumber, AcademicYear, EthnicOrigin)
      SELECT AdmissionNumber, AcademicYear,
      CASE WHEN AcademicYear = 2013 THEN '13' + CAST(EthnicOrigin AS NVARCHAR(2))
            ELSE '12' + CAST(EthnicOrigin AS NVARCHAR(2)) END AS EthnicOrigin
      FROM NextStudentPersonalTable
      WHERE AdmissionNumber IS NOT NULL AND LeavingDate = @LeavingDate
END

vivekkumarSharma

This is an issue in SQL Server parser.

Parser can't predict that which portion is going to execute during program execution. so it parse all the blocks of if... else if ... else at once and creates parse tree.

That's why It generates that error.

Possible solution:

Enclose all portions in dynamic query, as parser don't parse dynamic query.

declare @LeavingDate date=null

IF @LeavingDate IS NULL
BEGIN
exec ('select * into #a from a')
END
ELSE if @LeavingDate  IS not NULL
begin
exec ('select * into #a from a')
end

Open in new window

I think this clear the confusion.

expert_dharam

Make correction at line number 8:

DELETE FROM ESASTable WHERE [Admission Number] = @AdNo

You are missing the FROM keyword.

vivekkumarSharma

FROM keyword is not mandatory in Delete statement.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial