CODEWITHSUNDEEP
sql-servert-sql
ternyk
ternyk

Reputation: 581

Empty statement in T-SQL

Is there an empty statement keyword in T-SQL in SQL Server 2005 or newer? Something like NULL statement in PL/SQL.

Upvotes: 37

Views: 22788

Answers (6)

Eric
Eric

Reputation: 11

EXEC( '' ) Effectively does nothing

DECLARE @value INT
IF @value IS NULL
BEGIN
  EXEC( '' )
END
ELSE SELECT @value

Upvotes: 1

PavelD
PavelD

Reputation: 11

while 1 = 0 break

In function I can not use:

if 1 = 0 print ''
if 1 = 0 set nocount on
if 1 = 0 waitfor delay '00:00'
if 1 = 0 throw 50000, '', 10
if 1 = 0 select 1
if 1 = 0 return

Labels or variables cannot be declared twice.

Upvotes: 0

gbn
gbn

Reputation: 432271

No. There is not a "No operation" equivalent.

  • For a stored proc, you'd have at least SET NOCOUNT ON.
  • For an IF/ELSE, if a condition is empty omit it

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453287

You can declare a label to do nothing.

DECLARE @value INT

IF @value IS NULL
BEGIN
no_op1: 
END

Upvotes: 57

Michael J Swart
Michael J Swart

Reputation: 3179

I also believe there are sometimes legitimate uses for a nothing script (automatically generated scripts for example).

Although it's an old thread, I'll put in my two cents. I think declaring a variable is one of the most benign statements you can use. The statement doesn't even show up in execution plans:

IF (@variable = 0)
BEGIN
    DECLARE @placeHolder BIT;
END

Upvotes: 8

JM.
JM.

Reputation: 688

ugly happens sometimes. I believe their is a valid use. In a lengthy/complicated decision branching structure with several if else statements, some of those statements may contain conditions in which you specifically desire no action. You also don't want those condition falling thru do the default else, where certain work is done. In that case, it's a valid use.

Here are two ways to do this - see B and C 

Declare @status as char(1) 
set @status = 'D'

If (@status = 'A')
    select 'Great!'

Else if (@status = 'B')
begin
    if null=null select null -- predicate never resolves true
end

Else if (@status = 'C')
    set @status = @status  -- set a variable to itself 

Else
    select 'Needs work!'

Note, this is an over-simplified example. It is best used for readability when conditions are complex.

Upvotes: 12

Related Questions