If NOT EXISTS in SQL not function

Question

I put this in the SQL Query editor of SQL Server 2008 and doesn't work:

If not EXISTS (Select * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= 'dbo' AND TABLE_NAME='PK01')
    CREATE TABLE [dbo].['PK01'] (Articulo varchar(MAX),Referencia varchar(MAX),Lote varchar(MAX),Cantidad Int,Ubicacion varchar(MAX));
    INSERT INTO [UBICACIONES].[dbo].['PK01'] (Articulo,Referencia,Lote,Cantidad,Ubicacion)VALUES ('998','kk','ll',0,'pp')
else
    UPDATE [UBICACIONES].[dbo].['PK01'] Set Cantidad = (Cantidad + 23) WHERE Articulo LIKE '998'

Error I am getting:

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'else'.

Possible duplicate of [SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE](https://stackoverflow.com/questions/11906506/sql-server-2008-if-not-exists-insert-else-update) — mrogers, Jul 03 '17 at 21:51
@mrogers no it isn't. `MERGE` would not be a suitable answer to this question. — Martin Smith, Jul 03 '17 at 22:19

score 4 · Answer 1 · answered Jul 03 '17 at 21:51

4

Your if statement only acts on the first statement after the IF unless you put a BEGIN/END around the statements. This is the reason I like to have BEGIN/ENDs around even one statement IFs.

If not EXISTS (Select * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= 'dbo' AND TABLE_NAME='PK01')
BEGIN
    CREATE TABLE [dbo].['PK01'] (Articulo varchar(MAX),Referencia varchar(MAX),Lote varchar(MAX),Cantidad Int,Ubicacion varchar(MAX));
    INSERT INTO [UBICACIONES].[dbo].['PK01'] (Articulo,Referencia,Lote,Cantidad,Ubicacion)VALUES ('998','kk','ll',0,'pp')
END
ELSE
BEGIN
    UPDATE [UBICACIONES].[dbo].['PK01'] Set Cantidad = (Cantidad + 23) WHERE Articulo LIKE '998'         
END

answered Jul 03 '17 at 21:51

SteveB

769
4
18

thanks for the answer, but when I execute for the first time the query, it creates a table that is called PK01 and after insert the row with the values described in the query. But when I execute a second query the sql server put the follow message " There is already an object named ''PK01'' in the database. " and didn't execute the else statement. – panki Jul 03 '17 at 21:59
2

@panki that's because the table that this creates will contain single quotes in the name, `['PK01']` should not have any quotes. – Martin Smith Jul 03 '17 at 22:17

score 0 · Answer 2 · answered Jul 03 '17 at 22:14

This is highly not desirable. You should really create tables before you use them. But, you can do this using dynamic SQL:

DECLARE @sql NVARCHAR(MAX);

If not EXISTS (Select * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'PK01')
BEGIN
    SET @sql = 'CREATE TABLE [dbo].[''PK01''] (Articulo varchar(MAX),Referencia varchar(MAX),Lote varchar(MAX),Cantidad Int,Ubicacion varchar(MAX))';
    EXEC sp_executesql @sql;

    SET @sql = '
    INSERT INTO [UBICACIONES].[dbo].[''PK01''] (Articulo,Referencia,Lote,Cantidad,Ubicacion)VALUES (''998'',''kk'',''ll'',0,''pp'')';
    EXEC sp_executesql @sql;
END;
else
BEGIN
    SET @sql = '
    UPDATE [UBICACIONES].[dbo].[''PK01''] Set Cantidad = (Cantidad + 23) WHERE Articulo LIKE ''998''  '  ;

    EXEC sp_executesql @sql;     
END;

Having done all that, I'll repeat. This seems like a really bad application design.

If NOT EXISTS in SQL not function

2 Answers2