Solved

SQL Server IF...ELSE Statements

Posted on 2013-06-13
8
417 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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:
Scott Pletcher 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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