IF statement from a temp table

Question

I have a temp table with some columns containing numbers. I want to use the columns in the temp table and compare them WITH an IF statement.

...


INSERT INTO #tmp
EXEC [p_GetComplete] @Car_ID

IF Col1 + Col2 = Col3
BEGIN
 PRINT 'OK'
END
ELSE 
BEGIN
 PRINT 'NO'
END

...

In my humble opinion if you're doing IFs in the database you're doing something wrong... SQL is a set based language, your procedural logic should be in your application not your storage. — Liath, Jan 29 '14 at 12:02
You must SELECT the values, is there always only one row, if not do you want to perform an action for each row matching the condition? If so what action? — Alex K., Jan 29 '14 at 12:02

score 1 · Accepted Answer · answered Jan 29 '14 at 12:04

SQL Is made to operate on SET, so avoid CASE WHEN and IF as long as you can:

You can do it this way (recommended):

SELECT 'Ok' FROM #tmp WHERE Col1+Col2=Col3
UNION ALL
SELECT 'NO' FROM #tmp WHERE NOT (Col1+Col2=Col3)

or this way:

SELECT CASE WHEN Col1+Col2=Col3 THEN 'Ok' ELSE NO FROM #tmp WHERE

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

You can use case statement, when wanting to insert different value to #tmp table, according to some conditions.

Such as on:

SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported

If you want searching every record, and do some printing, there is good example on: http://technet.microsoft.com/en-us/library/ms180152.aspx

Good luck!!!

edited May 23 '17 at 12:03

Community

1
1

answered Jan 29 '14 at 12:09

Eitan

1,286
2
16
55

score 0 · Answer 3 · answered Jan 29 '14 at 12:22

Presumably, the exec is only returning one row and the only way you can capture the data is into a table. You can rewrite the stored procedure to have proper OUTPUT parameters, but let's assume that's not possible.

You can do what you want as:

INSERT INTO #tmp
EXEC [p_GetComplete] @Car_ID

IF exists(select * from #temp where Col1 + Col2 = Col3)
BEGIN
 PRINT 'OK';
END
ELSE 
BEGIN
 PRINT 'NO';
END;

That is, if a row exists where the condition is true, then print 'OK'.

IF statement from a temp table

3 Answers3