Click here to Skip to main content
15,881,803 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Comments
Mehdi Gholam 25-Sep-11 1:45am    
You are better off doing this kind of validations outside of SQL.

1 solution

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
 
Share this answer
 
Comments
udaysimha 13-Oct-11 9:28am    
Hi kishorekke,

Thanks for your reply for may post.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900