how to drop and create a table in a stored procedure?

Question

I have a stored procedure which will create a new table table1A%yyyymm% by duplicating the structure of an old table, table1. But before I create the new table, I need to check if the table exist or not, if exists, I need to drop the table first. Here is my stored procedure.

DECLARE @yyyymm char(6)
SET @yyyymm=CONVERT(nvarchar(6), GETDATE(), 112)


DECLARE @tableName varchar(50)
SET @tableName='table1A_' + @yyyymm 

DECLARE @SQL varchar(max)
SET @SQL = 'DROP TABLE ' + @tableName

IF EXISTS (SELECT * FROM sys.tables WHERE type='u' and name =@tableName)
EXEC(@SQL) print (@sql)


SET @SQL= 'SELECT TOP 0 * 
INTO ' + @tableName + ' FROM table1 with (nolock)'
EXEC(@SQL)

But if the table exists, I always got an error like following. Where is wrong?

DROP TABLE table1A_201404
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table1A_201404' in the database.

See http://stackoverflow.com/questions/167576/sql-server-check-if-table-exists — rory.ap, Apr 18 '14 at 16:10
Why would you want to create a table in your stored procedure. Is there reason why you don't use temp tables? — bobthedeveloper, Apr 18 '14 at 16:17

score 2 · Accepted Answer · answered Apr 18 '14 at 17:42

Had me stumped... Seems the "If Exists" does not work well together with exec in this case. Updated script below:

DECLARE @yyyymm char(6)
SET @yyyymm=CONVERT(nvarchar(6), GETDATE(), 112)

DECLARE @tableName varchar(50)
SET @tableName='table1A_' + @yyyymm 

DECLARE @SQL varchar(max)
SET @SQL = 'DROP TABLE ' + @tableName

EXEC('IF EXISTS(SELECT * FROM sys.tables WHERE type=''u'' and name = N''' + @tablename + ''') DROP TABLE table1A_201404')

SET @SQL= 'SELECT TOP 0 * 
INTO ' + @tableName + ' FROM sysobjects with (nolock)'
EXEC(@SQL) print @SQL

how to drop and create a table in a stored procedure?

1 Answers1

Linked

Related