Conditional SQL

• Mark Harley

  SSCrazy

  Points: 2259

  More actions

  April 27, 2006 at 10:03 am

  #171310

  I am trying to write an stored procedure that checks for the existance of a table.  If the table exists, then a record is entered, and if not then the table is first created and then the record is entered.  The catch is that the table name should be passed into the procedure.

   

  I understand that I can use dynamic sql to build my statements (ex: SET @var1 = 'INSERT INTO ' + @tablename...), but how do I work the conditions into it?

   

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  April 27, 2006 at 11:16 am

  #634659

  Try this?

  IF NOT EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = @tablename AND xtype = 'U')

  BEGIN

  SET @var1 = 'CREATE TABLE ' + @tablename

  EXEC (@var1)

  END

  SET @var1 = 'INSERT INTO ' + @tablename + ' VALUES(...

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  April 27, 2006 at 11:58 am

  #634670

  DECLARE

  @var1 varchar(50),    

  @tablename varchar(25),

  set @tablename = 'test'

   

  If OBJECT_ID(@tablename) is NULL

  BEGIN

    SET @var1 = 'CREATE TABLE ' + @tablename

    EXEC (@var1)

  END

  --then run your insert