Empty statement in T-SQL

Question

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

Solution 1

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

Solution 2

You can declare a label to do nothing.

DECLARE @value INT

IF @value IS NULL
BEGIN
no_op1: 
END

Solution 3

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.

Solution 4

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

View more solutions

Comments

[ternyk]

OK. Thanks. I need it for my SQL generator. But anyway it seems that I must remove an empty branch (IF/ELSE, CASE) earlier, from my intermediate objects. I found that empty statement can be "emulated" by an empty block (BEGIN END) but it doesn't look good.

[Cosmin Prund]

Thank you, I think this is the best way to do nothing because it really does nothing, it's declarative only. I'm going to use this for my automated translation of stored procedure code from Firebird to SQL-Server, because Firebird allows empty blocks while SQL-Server does not. At least for the cases where the begin-end block can't be trivially removed.

[syclee]

yep i think the 'if null = null select null' is as close to the // NOOP empty cases in a normal C type language

[sotn]

Great. And note that same label cannot be defined more than once.. That's why you should name it as no_op1 2 3...

[Lukas Eder]

I really like the if null = null select null version, because it's repeatable within a scope, in case this needs to be done more often and it doesn't have any side effects

[Elaskanator]

My use case is just like this person's: social.msdn.microsoft.com/Forums/sqlserver/en-US/… To have temporary debug statements that can be quickly commented out. I have since switched to a different pattern.

[Elaskanator]

For reference, MSDN explicitly says that empty BEGIN...END blocks are disallowed: msdn.microsoft.com/en-us/library/ms189079(sql.90).aspx

[it3xl]

It is not the best way. Labels is not repetitive. They have to be unique an entire batch (GO), otherwise they causes exceptions

[it3xl]

This approach is not for repetitions. You can't declare a variable twice during a batch (GO).

[it3xl]

IF NULL = NULL SELECT NULL and nothing more. Thanks!

[Martin Smith]

@LukasEder if null>null would be safer. null=null can resolve to true if SET ANSI_NULLS OFF

[Lukas Eder]

@MartinSmith: Nice catch! Of course, if 1 = 0 might be even more straightforward. null > null might need an extra comment to explain what looks very unusual

[Martin Smith]

@it3xl so just increment the 1 suffix. Or use a GUID in the label name generation process