set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goalter PROCEDURE [dbo].[P_GetForecastReport]
@departments varchar(200),
@quantity int
AS
BEGIN
CREATE TABLE #ProductForecast(
id int identity(1,1),
productName nvarchar(50),
)
begin
declare @a varchar(20)
declare @b varchar(20)
declare @c varchar(20)
declare @index int
declare @i int
declare @column varchar(20)
declare @columns varchar(50)
set @i = 1
set @columns = @departments
while @i < @quantity
begin
set @index = CHARINDEX(',', @columns)
set @column = substring(@columns,1,@index-1)
set @columns = substring(@columns ,@index+1,len(@column)-@index)
exec('alter table #ProductForecast add '+@column+' int')
set @i=@i+1
end
exec('alter table #ProductForecast add '+@columns+' int')
end
SELECT * FROM #ProductForecast
END
为什么不会根据字符串生成多个列呢,只生成了一个列
用的时候还提示传递到 SUBSTRING 函数的长度参数无效。
set QUOTED_IDENTIFIER ON
goalter PROCEDURE [dbo].[P_GetForecastReport]
@departments varchar(200),
@quantity int
AS
BEGIN
CREATE TABLE #ProductForecast(
id int identity(1,1),
productName nvarchar(50),
)
begin
declare @a varchar(20)
declare @b varchar(20)
declare @c varchar(20)
declare @index int
declare @i int
declare @column varchar(20)
declare @columns varchar(50)
set @i = 1
set @columns = @departments
while @i < @quantity
begin
set @index = CHARINDEX(',', @columns)
set @column = substring(@columns,1,@index-1)
set @columns = substring(@columns ,@index+1,len(@column)-@index)
exec('alter table #ProductForecast add '+@column+' int')
set @i=@i+1
end
exec('alter table #ProductForecast add '+@columns+' int')
end
SELECT * FROM #ProductForecast
END
为什么不会根据字符串生成多个列呢,只生成了一个列
用的时候还提示传递到 SUBSTRING 函数的长度参数无效。
set @columns = substring(@columns ,@index+1,len(@column)-@index)
应该是:
set @columns = substring(@columns ,@index+1,len(@columns)-@index)
while CHARINDEX(',', @columns) <> 0
begin
set @index = CHARINDEX(',', @columns)
set @column = substring(@columns,1,@index-1)
set @columns = substring(@columns ,@index+1,len(@columns)-@index)
exec('alter table #ProductForecast add '+@column+' int')
end
exec('alter table #ProductForecast add '+@columns+' int')
...
if @column =''