Hi I am writing a large stored procedure, which creates a dynamic report table, of n columns in size, the first 6 are constant the remainder depend on a few arguments passed to the procedure to create the table with the required columns.
您好我正在编写一个大型存储过程,它创建一个动态报表,大小为n列,前6个是常量,其余依赖于传递给过程的几个参数来创建具有所需列的表。
The problem that I am having is with the following TSQL
我遇到的问题是使用以下TSQL
DECLARE @columnname VARCHAR(50)
SET @columnname = 'on_' + @description
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('reports')
AND NAME = @columnname)
BEGIN
ALTER TABLE reports ADD @columnname VARCHAR(50) NULL
END
I am getting syntax errors with this at the @columnname in the ALTER TABLE statement of the above code.
我在上面代码的ALTER TABLE语句中的@columnname上遇到了语法错误。
Also as I am new to this, I am not sure if this is the best way to do this, or if there are better ways in TSQL to generate the required dynamic table.
另外,由于我是新手,我不确定这是否是执行此操作的最佳方式,或者是否有更好的方法在TSQL中生成所需的动态表。
3 个解决方案
#1
Try this:
declare @sql nvarchar(100)
声明@sql nvarchar(100)
set @sql = 'ALTER TABLE reports ADD '+ @columnname+' VARCHAR(50) NULL'
set @sql ='ALTER TABLE报告ADD'+ @ columnname +'VARCHAR(50)NULL'
exec sp_executesql @sql
exec sp_executesql @sql
#2
Try
DECLARE @columnname VARCHAR(50)
SET @columnname = '[on_' + @description +']'
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('reports')
AND NAME = @columnname)
BEGIN
ALTER TABLE reports ADD @columnname VARCHAR(50) NULL
END
#3
Cannot get around having to do it dynamically I believe so change your BEGIN block to something like this:
无法绕过必须动态地执行它我相信所以将您的BEGIN块更改为以下内容:
DECLARE @sql VARCHAR(8000)
BEGIN
SET @sql = 'ALTER TABLE Table_1 ADD '+@columnname+' VARCHAR(50) NULL'
EXEC(@sql)
END
#1
Try this:
declare @sql nvarchar(100)
声明@sql nvarchar(100)
set @sql = 'ALTER TABLE reports ADD '+ @columnname+' VARCHAR(50) NULL'
set @sql ='ALTER TABLE报告ADD'+ @ columnname +'VARCHAR(50)NULL'
exec sp_executesql @sql
exec sp_executesql @sql
#2
Try
DECLARE @columnname VARCHAR(50)
SET @columnname = '[on_' + @description +']'
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('reports')
AND NAME = @columnname)
BEGIN
ALTER TABLE reports ADD @columnname VARCHAR(50) NULL
END
#3
Cannot get around having to do it dynamically I believe so change your BEGIN block to something like this:
无法绕过必须动态地执行它我相信所以将您的BEGIN块更改为以下内容:
DECLARE @sql VARCHAR(8000)
BEGIN
SET @sql = 'ALTER TABLE Table_1 ADD '+@columnname+' VARCHAR(50) NULL'
EXEC(@sql)
END