DECLARE @DROP VARCHAR(MAX)
DECLARE @TAB VARCHAR(MAX)='Employees'
DECLARE @CREATE VARCHAR(MAX)
DECLARE @SELECT VARCHAR(MAX)
--IF OBJECT_ID('DBO.'+@TAB,'U') IS NOT NULL
--BEGIN
IF (EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @TAB))
BEGIN
SET @DROP = N'DROP TABLE '+@TAB
EXEC(@TAB)
END
SET @CREATE= 'CREATE TABLE '+@TAB+
'(
ID INT
,NAME VARCHAR(50)
,PHONE VARCHAR(25)
,ADDRESS VARCHAR(100)
)'
EXEC(@CREATE)
SET @SELECT='SELECT * FROM '+@TAB
EXEC(@SELECT)
我得到一个错误,为什么?
消息2809,级别16,状态1,行1
由于对“雇员”是一个表对象,对过程“雇员”的请求失败。
您只犯了一个错误,而不是使用EXEC(@TAB),应该使用EXEC(@DROP)
DECLARE @DROP VARCHAR(MAX)
DECLARE @TAB VARCHAR(MAX)='Employees'
DECLARE @CREATE VARCHAR(MAX)
DECLARE @SELECT VARCHAR(MAX)
--IF OBJECT_ID('DBO.'+@TAB,'U') IS NOT NULL
--BEGIN
IF
(EXISTS
(
SELECT
1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @TAB
)
)
BEGIN
SET @DROP=N'DROP TABLE '+@TAB
print @DROP
EXEC(@DROP)
END
SET @CREATE=
'CREATE TABLE '+@TAB+
'(
ID INT
,NAME VARCHAR(50)
,PHONE VARCHAR(25)
,ADDRESS VARCHAR(100)
)'
EXEC(@CREATE)
SET @SELECT='SELECT * FROM '+@TAB
EXEC(@SELECT)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句