Solved

SQL Server IF...ELSE Statements

Posted on 2013-06-13
8
414 Views
Last Modified: 2013-07-05
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!
0
Comment
Question by:Karl_mark
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39244225
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.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 39244239
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.
0
 

Author Comment

by:Karl_mark
ID: 39244276
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

0
 
LVL 12

Expert Comment

by:sachitjain
ID: 39244454
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
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39244460
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.
0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 39244466
Make correction at line number 8:

DELETE FROM ESASTable WHERE [Admission Number] = @AdNo

You are missing the FROM keyword.
0
 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39244476
FROM keyword is not mandatory in Delete statement.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39245246
IF/ELSE BEGIN/END:

The rule in SQL Server is that a single statement does not require BEGIN and END around it, but 2 or more statements do.  For example:

IF @x = 1
    SET @y = 2

but:

IF @x = 1
BEGIN
    SET @y = 2
    SET @z = 3
END --IF

w/o the BEGIN/END, the "SET @z = 3" would always execute.

You are allowed to use BEGIN/END around a single statement if you prefer, so below is perfectly OK:

IF @x = 1
BEGIN
    SET @y = 2
END
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now