• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

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!
0
Karl_mark
Asked:
Karl_mark
1 Solution
 
Patrick MatthewsCommented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
Karl_markAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sachitjainCommented:
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
 
vivekkumarSharmaCommented:
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
 
expert_dharamCommented:
Make correction at line number 8:

DELETE FROM ESASTable WHERE [Admission Number] = @AdNo

You are missing the FROM keyword.
0
 
vivekkumarSharmaCommented:
FROM keyword is not mandatory in Delete statement.
0
 
Scott PletcherSenior DBACommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now