在存储过程中创建表
我要再存储过程中创建表，但是表名是个变量，如：
CREATE PROCEDURE TableCreate
@ID int
AS
Declare @TableName varchar(50)
set @TableName = 'Table ' + Convert(varchar(50),@ID)
create table @TableName ( tableName varchar(20) not null )
但是SQL 不允许表名是变量，我该如何处理？
[解决办法]
CREATE PROCEDURE TableCreate
@ID int
AS
Declare @TableName varchar(50)
set @TableName = 'Table ' + Convert(varchar(50),@ID)
exec( 'create table '+@TableName+ ' ( tableName varchar(20) not null ) ')
[解决办法]
CREATE PROCEDURE TableCreate
@ID int
AS
Declare @TableName varchar(50),@sql varchar(8000)
set @TableName = 'Table ' + Convert(varchar(50),@ID)
set @sql= 'create table '+@TableName+ '( tableName varchar(20) not null ) '
exec(@sql)
[解决办法]
CREATE PROCEDURE TableCreate
@ID int
AS
Declare @SQL varchar(5000)
set @SQL= 'create table Table '+rtrim(@id)+ ' ( tableName varchar(20) not null ) '
exec(@SQL)
[解决办法]
CREATE PROCEDURE TableCreate
@ID int
AS
Declare @SQL varchar(5000)
set @SQL= 'create table Table '+rtrim(cast(@id as varchar(100))+ ' ( tableName varchar(20) not null ) '
exec(@SQL)
[解决办法]
要用到动态SQL创建.
alter PROCEDURE TableCreate
@ID int
AS
Declare @TableName varchar(50)
declare @sql varchar(8000)
/*
set @TableName = 'Table ' + Convert(varchar(50),@ID)
create table @TableName ( tableName varchar(20) not null )
*/
set @sql= 'create table Table '+rtrim(@ID)+ '( tableName varchar(20) not null ) '
exec(@sql)
exec TableCreate '123 '
