CODEWITHSUNDEEP
sqlsql-servert-sqlinsertcommon-table-expression
ahmd0
ahmd0

Reputation: 17293

Conditional INSERT following a CTE clause in t-SQL

I was curious why I can't seem to get a conditional statement to follow a common table expression in t-SQL statement?

Stuff like this:

WITH ctx AS(...)
IF ctx.v BEGIN
INSERT INTO ...
END

I made a sample fiddle here.

Upvotes: 1

Views: 1985

Answers (1)

Bert
Bert

Reputation: 82459

A common table expression is basically just a subquery that can be reused in multiple SQL statements. So, you would need to select from it in any case; you can't just reference it like it's a variable. Think of it more like a temporary table.

You should be able to achieve what you want just by adding your criteria as a where clause.

WITH ctx AS(
   SELECT MIN(t1.dtIn) AS mdIn, MAX(t1.dtOut) AS mdOut FROM tbl t1
      INNER JOIN tbl t2
      ON t1.Type = t2.Type
        AND t1.dtIn < t2.dtOut 
        AND t1.dtOut > t2.dtIn
      WHERE t1.type = 1
)

INSERT INTO tbl (id, dtIn, dtOut, type)
SELECT 10, 
       CASE WHEN mdOut IS NOT NULL AND 
                 mdOut > '0:30' AND
                 '0:40' >= mdOut THEN mdOut
       ELSE '0:30' END,
       CASE WHEN mdIn IS NOT NULL AND
                 mdIn < '0:40' AND
                 '0:30' >= mdIn THEN mdIn
       ELSE '0:40' END,
1
FROM ctx
where ctx.mdIn < '0:40' AND ctx.mdOut > '0:30'

select * from tbl

Upvotes: 3

Related Questions