TSQL: if statement inside a select insert

Question

I have this structure inside a function:

INSERT INTO @TheTable
    SELECT DISTINCT

        @Number AS Number,

        @Name AS Name

    FROM MYTABLE 
    WHERE id = @id

I need to add to this structure the result of executing another function and I need your help to do so.

Below the @Name AS Name, line I should add something like this

IF (dbo.anotherFunction(@id)==1) 
@NewVisitor AS NewVisitor
ELSE
@NewVisitor AS NoNewVisitor

How can I translate this into TSQL??

Thanks a million!

So your `INSERT` doesn't reference any columns in `MYTABLE` and you bring back as many rows as there are `id` matches then get rid of all but one with `DISTINCT`? You would be much better off just using an `IF EXISTS (...)` — Martin Smith, Jan 24 '12 at 16:59
are newVisitor and noNewVisitor 2 different columns in @TheTable? So you want to insert @newVisitor into one of 2 columns? — Beth, Jan 24 '12 at 17:00
it's just an example, please teach me how to do the if clause or similar inside that structure. Many thanks — user712027, Jan 24 '12 at 17:01
You'll want a case statement, discussed [here](http://stackoverflow.com/q/206484/1078151) — That Chuck Guy, Jan 24 '12 at 17:02

score 7 · Accepted Answer · answered Jan 24 '12 at 17:02

Guessing this is what you want...

INSERT INTO @TheTable
    SELECT DISTINCT
        @Number AS Number,
        @Name AS Name,
        case when (dbo.anotherFunction(@id)=1) then @NewVisitor else null end as NewVisitor,
        case when (dbo.anotherFunction(@id)<>1) then @NewVisitor else null end AS NoNewVisitor
    FROM MYTABLE 
    WHERE id = @id

score 1 · Answer 2 · answered Jan 24 '12 at 17:02

1

IF statements aren't available inside of SELECT statements. Instead, you'll want to utilize a CASE.

MSDN Documentation on CASE

answered Jan 24 '12 at 17:02

Dave Simione

1,441
2
21
31

score 0 · Answer 3 · answered Jan 24 '12 at 17:04

This question makes little sense to me (why all the variables, what do the columns mean?), but you can do it this way:

IF (dbo.anotherFunction(@id)==1) 
    INSERT INTO @TheTable
    SELECT DISTINCT
        @Number AS Number,
        @Name AS Name,
        @NewVisitor AS NewVisitor
    FROM MYTABLE 
    WHERE id = @id
ELSE
    INSERT INTO @TheTable
    SELECT DISTINCT
        @Number AS Number,
        @Name AS Name,
        @NewVisitor AS NoNewVisitor
    FROM MYTABLE 
    WHERE id = @id

Alternatively use two CASE statements to selected NewVisitor and NoNewVisitor.

TSQL: if statement inside a select insert

3 Answers3