CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Joe
Joe

Reputation: 1305

Terminate IF block in Transact-SQL

This is probably quite a rookie question, but I can't find in the MSDN examples online of how to end an IF/ELSE block in Transact-SQL. For example, I want to create a SP that does this:

USE [MyDb];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[usp_MyStoredProc]
@param1 DATE, @param2 INT, @param3 BIT, @param4 [dbo].[CustomDataType] READONLY
WITH EXEC AS CALLER
AS
-- Only run this part if @param3 == true
IF (@param3 = 1)
   BEGIN
     --do stuff...
   END
-- I don't really need an else here, as I'm simply doing an 
-- extra step at the beginning if my param tells me to

-- I always want this next part to run...
-- do more stuff...
GO

I have a solution that I think works (posted as answer below), but I feel like I'm working around my lack of knowledge instead of doing the best pattern.

Edit- I deleted my first solution as it was a horrible idea and didn't want others to try implementing that. Turns out the question was actually the solution in this case; thanks to all.

Upvotes: 0

Views: 223

Answers (2)

Yatrix
Yatrix

Reputation: 13785

There doesn't have to be an else statement. Whatever's between the BEGIN/END will be run if it meets the IF condition and then your SQL will progress normally after that. It's the same thing as:

If (x == 1) {
   // condition met, do stuff
}
// do more stuff

You were really close. Just needed some tweaking. 

IF (@param3 = 1)
  BEGIN
  -- do stuff if a param tells me to (has to be done before main block of code is ran below)
  END    
-- do stuff that should always be ran.
GO

Do yourself and your team a favor: never use a goto unless it's 100% necessary. Usually, they are not necessary with well-structured code.

Upvotes: 2

Sebastian Meine
Sebastian Meine

Reputation: 11813

The syntax for the IF statement is

IF Boolean_expression 
     { sql_statement | statement_block } 
[ ELSE 
     { sql_statement | statement_block } ]

The ELSE part is optional.

A "sql_statement" is any single statement. A "statement_block" is a group of statements wrapped in a BEGIN and END

More details:http://msdn.microsoft.com/en-us/library/ms182717.aspx

Upvotes: 3

Related Questions