How to create an IF that does two things one after the other in SQL

user_11808836 0
Viewed 52

I have code like this:

  IF (@schema NOT IN (SELECT TABLE_SCHEMA
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_CATALOG = 'Testowa' 
                     ))
    BEGIN
    declare @sql nvarchar(MAX) = 'create schema ' +@schema+ '' 
    EXEC sp_executesql @sql
          EXEC(@Sql1)
          EXEC(@Sql2)
          EXEC(@Sql3)
          EXEC(@Sql4)
          EXEC(@Sql5)
          EXEC(@Sql6)
          EXEC(@Sql7)
          EXEC(@Sql8)
    END
    ELSE

It makes everything in one moment, I want to make some breaks after @sql and after @sql6 I tried with 'GO', but 'GO' stops IF. 'Breaks' = First do @sql After that Make @sql1-6 After that @Sql7-8. How should I make it?

sql-server sql database
2 Answers

It could be rewritten as:

IF NOT EXISTS (SELECT 1
               FROM INFORMATION_SCHEMA.SCHEMATA
               WHERE CATALOG_NAME = 'Testowa'
                 AND SCHEMA_NAME = @schema)
BEGIN
  DECLARE @sql NVARCHAR(MAX) = 'CREATE SCHEMA  ' + QUOTENAME(@schema);
  PRINT @sql;
  EXEC(@sql);
  WAITFOR DELAY '00:05';   -- "break"

  EXEC(...);
  EXEC(...);

  WAITFOR DELAY '00:05'; 

  EXEC(...);
END;

Notes:

  • ; are recommended
  • variable provided into dynamic SQL should be parametrized sp_executesql, if they are concatenated identifiers QUOTENAME should be used
  • WAITFOR as "break"
  • to find if schema exists INFROMATION_SCHEMA.SCHEMATA could be used instead of TABLES
  • PRINT as debug
user_50708790

This looks ugly but would do what you want:

SELECT CASE WHEN @schema NOT IN
                   (
                     SELECT TABLE_SCHEMA
                     FROM   INFORMATION_SCHEMA.TABLES
                     WHERE  TABLE_CATALOG='Testowa'
                   )
            THEN 1
       ELSE 0
       END AS schemaExists
INTO   #tmp;

IF EXISTS (SELECT * FROM #tmp WHERE schemaExists=0)
  BEGIN
    DECLARE @sql NVARCHAR(MAX) =N'create schema '+@schema+N'';
    EXEC sp_executesql @sql;
  END;
GO
IF EXISTS (SELECT * FROM #tmp WHERE schemaExists=0)
  BEGIN
    EXEC(@Sql1);
    EXEC(@Sql2);
    EXEC(@Sql3);
    EXEC(@Sql4);
    EXEC(@Sql5);
    EXEC(@Sql6);
  END;
GO

IF EXISTS (SELECT * FROM #tmp WHERE schemaExists=0)
  BEGIN
    EXEC(@Sql7);
    EXEC(@Sql8);
  END;
GO
IF NOT EXISTS (SELECT * FROM #tmp WHERE schemaExists=0) -- ELSE
  BEGIN
    -- 
    PRINT 'Blah Blah';
  END;
GO
DROP TABLE #tmp;
user_8949770
Related