如何循环在SQL Server 2005中存储过程内部创建的动态临时表的列 [英] How to loop columns of dynamic temporary table which is created inside storedprocedure in sql server 2005

问题描述

大家好，


请任何人帮我循环在存储过程内部创建的动态临时表的列，以捕获列名并检查某些条件，以便仅在sql server 2005中将某些数据插入该存储过程内部的动态临时表中.

在此先感谢
Uday

Hi all,


Please can anybody help me to loop columns of dynamic temporary table which is created inside storedprocedure to capture the column name and to check some conditions to insert some data into that dynamic temporary table inside stored procedure only in sql server 2005.

Thanks in advance
Uday

推荐答案

请检查以下代码，以使用SQL查询为表动态创建列.
"#tClients"是一个临时表，根据搜索条件具有帐户(此表可能具有多个行)

-使用DYNAMIC列创建表
创建表Clientslist(EmpUID int，EmployeeName varchar(50)，JobStatus varchar(25)，JobCode char(2)，
类别char(1)，TotalLines十进制(18,5)，Supervisor varchar(50)，
虚拟位)
将@Script声明为Varchar(8000)
将@Script_prepare声明为Varchar(8000)
Set @Script_prepare =``更改表Clientslist添加[?] varchar(100);''
设置@Script =''''
从#tClients中选择@Script = @Script + Replace(@Script_prepare，``?''，[Code]) 执行(@Script)
更改表Clientslist删除列虚拟


-动态地进行一些操作
宣告@i int，@ SQL varchar(4000)，@ TableName sysname，@ ColumnName sysname，@ rc int
SET @i = 8-动态列的起始位置

SELECT ORDINAL_POSITION，TABLE_NAME，COLUMN_NAME
INTO#临时
来自INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =``客户列表''

从#Temp WHERE ORDINAL_POSITION = @i
中选择SELECT @TableName = TABLE_NAME，@ColumnName = COLUMN_NAME SET @rc = @@ ROWCOUNT
@rc<> 0
开始

DECLARE @t_Lines十进制(18,2)

-这里我要更新表格中动态列的行数
SET @ t_Lines = 0
SELECT TOP 1 @ t_Lines = cast(行作为小数(18,2))来自#tEmpClient WHERE Category =''T''
从#tEmpClient WHERE SNo =中删除(从#tEmpClient WHERE Category =''T''中选择前1个SNO)
SET @SQL =``UPDATE''+ @TableName +''SET''+ @ColumnName +''=``+ Convert(varchar，@ t_Lines)+''WHERE Category =''''T'''' AND EmpUID =''+转换(varchar，@ t_EmpUID)+''''
EXEC(@SQL)


SET @i = @i +1
从#Temp WHERE ORDINAL_POSITION = @i
中选择SELECT @TableName = TABLE_NAME，@ColumnName = COLUMN_NAME SET @rc = @@ ROWCOUNT
END

选择*来自客户列表

删除表#Temp
DROP TABLE Clientslist

Please check the below code to create columns dynamically for a table using SQL query.
"#tClients" is a temp table having accounts as per search criteria ( this table may have mutiple rows)

--create table with DYNAMIC columns
create table Clientslist( EmpUID int, EmployeeName varchar(50),JobStatus varchar(25), JobCode char(2),
Category char(1),TotalLines decimal(18,5), Supervisor varchar(50),
dummy bit)
Declare @Script as Varchar(8000)
Declare @Script_prepare as Varchar(8000)
Set @Script_prepare = ''Alter table Clientslist Add [?] varchar(100);''
Set @Script = ''''
Select @Script = @Script + Replace(@Script_prepare, ''?'', [Code]) From #tClients
Exec (@Script)
Alter table Clientslist drop column dummy


--to do DYNAMICALLY some operations
DECLARE @i int, @SQL varchar(4000), @TableName sysname, @ColumnName sysname, @rc int
SET @i = 8 --Starting position of dynamic columns

SELECT ORDINAL_POSITION, TABLE_NAME, COLUMN_NAME
INTO #Temp
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''Clientslist''

SELECT @TableName = TABLE_NAME, @ColumnName = COLUMN_NAME FROM #Temp WHERE ORDINAL_POSITION = @i
SET @rc = @@ROWCOUNT
WHILE @rc <> 0
BEGIN

DECLARE @t_Lines decimal(18,2)

--here I am updating linecounts in the table for dynamic columns
SET @t_Lines=0
SELECT TOP 1 @t_Lines=cast(Lines as decimal(18,2)) FROM #tEmpClient WHERE Category=''T''
DELETE FROM #tEmpClient WHERE SNo=(SELECT TOP 1 SNO FROM #tEmpClient WHERE Category=''T'')
SET @SQL = ''UPDATE '' + @TableName + '' SET '' + @ColumnName + '' = ''+Convert(varchar,@t_Lines) + '' WHERE Category=''''T'''' AND EmpUID=''+convert(varchar,@t_EmpUID)+''''
EXEC (@SQL)


SET @i = @i + 1
SELECT @TableName = TABLE_NAME, @ColumnName = COLUMN_NAME FROM #Temp WHERE ORDINAL_POSITION = @i
SET @rc = @@ROWCOUNT
END

SELECT * FROM Clientslist

DROP TABLE #Temp
DROP TABLE Clientslist

这篇关于如何循环在SQL Server 2005中存储过程内部创建的动态临时表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！