如何在存储过程中删除和创建表?
问题描述：
我有一个存储过程，它将通过复制旧表 table1 的结构来创建一个新表 table1A%yyyymm%.但是在创建新表之前，我需要检查该表是否存在，如果存在，我需要先删除该表.这是我的存储过程.
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.
答
让我难住了...在这种情况下，似乎如果存在"与 exec 不能很好地协同工作.更新脚本如下:
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