loop on column value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

loop on column value

Author

Topic

maleksafa
Starting Member

1 Post

Posted - 2011-09-29 : 09:05:23

declare @st varchar(max)
declare @ss varchar(max)
hello, i have a table that contains only one row but many columns, i want to loop on the columns to find the criteria that m looking for:

declare @i int
declare @Ttype varchar(15)
declare @v int

set @i = 3
set @v = 3

while (@i < 35)
BEGIN
--HERE I LOOP ON THE COLUMNS NAME

set @st = (select column_name
from information_schema.columns WHERE TABLE_NAME = 'F590036N' and TABLE_SCHEMA = 'TESTDTA' and ORDINAL_POSITION = @i)

--FOR EACH COLUMN NAME I INSERT IT IN Ttyope, the problem is that
--it's returning the column name and not the column value

select @Ttype = @st from testdta.f590036n where DSRJOB = 1
print (@Ttype)

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-09-29 : 11:35:42

Have a look at this. It is based on your table name of TESTDTA. If you actual table is called something else, do a search and replace to change all references to this.


--drops table if already exists
IF Object_ID('tmpFieldNames') IS NOT NULL
BEGIN
	DROP TABLE tmpFieldNames
END

--build a table of field names for the table TESTDTA i the current server
DECLARE @SQLAddToTable VARCHAR(1000)
SET @SQLAddToTable = '
		SELECT col.Name as FieldName
			,ROW_NUMBER() OVER (ORDER BY ob.Name) AS Row
		INTO tmpFieldNames
		FROM sysobjects AS ob
		INNER JOIN dbo.syscolumns AS col ON ob.ID=col.ID		
		WHERE ob.xType = ''U''
		AND ob.Name LIKE ''TESTDTA'''		
EXEC (@SQLAddToTable)

--build counter for loop
DECLARE @Counter INT
SET @Counter = 1

--initialize variable
DECLARE @SQLSEL VARCHAR(1000)
SET @SQLSEL = 'SELECT '

--run through your temp table. with each iteration add the selected fieldname to the SELECT statement
BEGIN
	WHILE @Counter <= (SELECT COUNT(*) FROM tmpFieldNames )
	-- runs once for each row in the table
	BEGIN
		SELECT @SQLSEL = @SQLSEL + FieldName + ', ' FROM tmpFieldNames WHERE Row = @Counter
		SET @Counter = @Counter +1
	END
END

--finish off the SELECT statement (The NULL is simply to account for the additional comma at the end of the fieldnames
SET @SQLSEL = @SQLSEL  + 'NULL FROM TESTDTA'

EXEC(@SQLSEL)

--housekeeping
DROP TABLE tmpFieldNames

I'm actually pretty chuffed with this piece of code. Any of you who can reach, please give me a pat on the back.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-09-29 : 11:39:28

Of course, having done all that I have just realised that you could simply use SELECT * FROM TESTDTA, but what's the fun in that eh?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-09-29 : 12:43:14

I expect nothing less from a Bury fan.

Now an Arsenal fan would have started with SELECT *

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-29 : 13:08:11

>>i have a table that contains only one row but many columns
I can't think of a good reason for this design. Why did you model it like this?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 13:55:48

if you want to get column value you need to use dynamic sql using sp_executesql
something like:


declare @sql nvarchar(4000),@params nvarchar(2000),@ret varchar(1000)
set @sql=N'select @Ttype = '+ @st + ' from testdta.f590036n where DSRJOB = 1'
set @params=N'@Ttype varchar(1000) OUT'

EXEC sp_executesql @sql,@params,@Ttype =@ret OUT
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources