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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop through rows?

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-18 : 12:21:13
Hi.
Strangely i cannot find a decent example.
I don't want to use cursors if possible.

I have a table
id, name, specialid
1,john,3
2,mike,3
4,mary,3
5,nick,6
6,john,6
8,kate,6

etc.
So i want a loop that will pick the, let's say the name column and the id column and will display all the names and id's that is on specialid =3 .
Do i need a temp table. I say a while that extended the id but the id's here are not in continues order so i can do a -1 .
Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-18 : 13:26:41
quote:
Originally posted by sapator

Hi.
Strangely i cannot find a decent example.
I don't want to use cursors if possible.

I have a table
id, name, specialid
1,john,3
2,mike,3
4,mary,3
5,nick,6
6,john,6
8,kate,6

etc.
So i want a loop that will pick the, let's say the name column and the id column and will display all the names and id's that is on specialid =3 .
Do i need a temp table. I say a while that extended the id but the id's here are not in continues order so i can do a -1 .
Thanks.

You can add a where clause to your query, e.g.,
SELECT id, name FROM YourTable WHERE specialid=3
In this example, that will return the 3 rows, and you can do whatever you need to do with those either as a group, or one row at a time.
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-18 : 13:55:32
I agree with James, but if you just were looking for looping logic alternative to a cursor (although some folks consider a loop as a cursor)

DECLARE @i int = 0

WHILE @i < 11
BEGIN

IF @i%2 = 0 -- can be any condition
BEGIN
SELECT @i
END

SET @i = @i+1

END

Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-18 : 16:25:40
Amen, avoid cursors whenever possible.

Try this instead:


-- create dummy table and load with original test data
CREATE TABLE dbo.test_data (
id int,
name varchar(30),
specialid int
)
INSERT INTO dbo.test_data
SELECT *
FROM (
SELECT 1 AS id,'john' AS name,3 AS specialid UNION ALL
SELECT 2,'mike',3 UNION ALL
SELECT 4,'mary',3 UNION ALL
SELECT 5,'nick',6 UNION ALL
SELECT 6,'john',6 UNION ALL
SELECT 8,'kate',6
) AS test_data


SELECT td.specialid, ca1.ids_and_names
FROM (
SELECT DISTINCT specialid
FROM dbo.test_data td
) AS td
CROSS APPLY (
SELECT STUFF((
SELECT ';' + CAST(td2.id AS varchar(10)) + ',' + td2.name
FROM dbo.test_data td2
WHERE
td2.specialid = td.specialid
ORDER BY td2.id
FOR XML PATH('')
), 1, 1, '') AS ids_and_names
) AS ca1

DROP TABLE dbo.test_data

Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-20 : 18:34:42
Hi. Let me explain a little better, so i need to parse the rows one by one (i don't care about the order) and do something with, let's say name and id.
So on Jame's answer, how would i do something on each row at a time. I suspect a temp table? Let's say i need to get the id on each row in the loop and just print it out, for testing.
Michael, when you say while @i<11 do i suspect that i need first to do a count on the rows and set @i with that? So i am not completely sure on how would i get the row values i need, @i will just loop the int.How would i get the id of the first row in?
Scott, can you show me where i put the logic to get, for example the row id, this will just put everything in a row. I don't to have to parse the created row, seems like a double job.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-21 : 08:40:09
quote:
Originally posted by sapator

Hi. Let me explain a little better, so i need to parse the rows one by one (i don't care about the order) and do something with, let's say name and id.
So on Jame's answer, how would i do something on each row at a time. I suspect a temp table? Let's say i need to get the id on each row in the loop and just print it out, for testing.
Michael, when you say while @i<11 do i suspect that i need first to do a count on the rows and set @i with that? So i am not completely sure on how would i get the row values i need, @i will just loop the int.How would i get the id of the first row in?
Scott, can you show me where i put the logic to get, for example the row id, this will just put everything in a row. I don't to have to parse the created row, seems like a double job.

Instead of thinking about HOW you want to accomplish something, think about WHAT you want to accomplish.

Instead of saying "Let's say i need to get the id on each row in the loop and just print it out, for testing", think about it as, "I want my end result to be a print out of all the ids in the table". To do that, all you have to use the code I posted earlier:
SELECT id  FROM YourTable
Or, let us say you want to increment the id of every row by 10. All you have to do is
UPDATE YourTable SET id = id+10;
Or, you want to add a period to the end of the name column in rows that have specialid = 3.
UPDATE YourTable SET name = name+'.' WHERE specialid=3
All of this works because SQL Server operates on sets. To make the most out of SQL Server, you have to learn to think in sets rather than thinking about one row at a time. In other words, think about WHAT rather than HOW.

Some interesting links you may find useful:
https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/
http://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/dp/0123741378
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-21 : 08:50:40
Hi.
Hence the problem.
I am having a dynamic SQL Sproc that auto generates a new table and columns.
Each column is unique and with different definitions.
I have to iterate through a set of pre - defined rowdefinitions found in another table and then file the newly created columns of the dynamic SQL on by one with these definitions, specific to each column.
What i am doing right now is, unfortunately, using cursors.
I was hoping i could turn that to a loop so i can avoid them.
So, from my point, i can't see it as a set, as the columns will just be created. So i cannot update something either.
My first example was simple so i didn't want to mess things up, so i guess i can put the cursor part now, to see if it's doable with a loop:

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
select fieldSize,fieldName from DBTempSyncScenarioFD
where ScenarioAA = @ScenarioAA
order by FieldPosition;
OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @field_size, @field_name;

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @dynamicColumn NVARCHAR(MAX) = -- @field_size, @field_name of
the dynamic created column ;
DECLARE @dynamicSQL NVARCHAR(MAX) =
-- alert the table and insert the dynamic column
EXECUTE (@dynamicSQL) ;

FETCH NEXT FROM @BusinessCursor INTO @field_size, @field_name;
END
CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-21 : 11:01:01
I am not really sure what you are trying to do. you seem to want to build a string to do some dynamic sql involving adding columns to a table??? again not sure.

-- for the loop, just add another variable and use your cursor select to get the count

DECLARE @z int = (SELECT COUNT(1) + 1 from DBTempSyncScenarioFD where ScenarioAA = @ScenarioAA )
DECLARE @i int = 0

WHILE @i < @z
BEGIN

IF @i%2 = 0 -- can be any condition
BEGIN
SELECT @i
END

SET @i = @i+1

END

Or-- if you are just looking to build a string from that table you can do something like the following:

-- set up
CREATE TABLE #DBTempSyncScenarioFD ( fieldSize varchar(20),fieldName varchar(50))

INSERT INTO #DBTempSyncScenarioFD values('int','Myint'),('varchar(20)','Myvarchar'),('varchar(50)','MyBiggervarchar'),('datetime','Mydatetime')


-- build a string
DECLARE @dynamicColumn NVARCHAR(MAX) = ''

SELECT @dynamicColumn = @dynamicColumn + ', ' + fieldName + ' ' + fieldSize FROM #DBTempSyncScenarioFD

SELECT SUBSTRING(@dynamicColumn,2,LEN(@dynamicColumn))
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-21 : 11:47:13
Yes adding columns to a table.
When you say "IF @i%2 = 0 -- can be any condition" that is the problem. I have no idea how would i change the fieldName and field_size. What condition do i need?
This will just loop a @i variable. How would i use it to do what i want?

On second example. Let's say i want to build SELECT @dynamicColumn = @dynamicColumn + ', ' + fieldName + ' ' + fieldSize FROM #DBTempSyncScenarioFD , again how would i loop to set each dynamic column?
Thanks.
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-21 : 12:38:22
you don't need that - I had to write some code in the loop for demonstration purposes - that code spits out even numbers using the mod function .. you would replace this code
IF @i%2 = 0 -- can be any condition
BEGIN
SELECT @i
END
With your code.

on the second example , you don't need to loop -- it will build the string. Run the example I posted from set up on and you will see it build a string. So , If I were trying to build a table dynamically, I could concatenate the string 'CREATE TABLE X (' + @dynamicColumn + ' )' and execute .. Example:

CREATE TABLE #DBTempSyncScenarioFD ( fieldSize varchar(20),fieldName varchar(50))

INSERT INTO #DBTempSyncScenarioFD values('int','Myint'),('varchar(20)','Myvarchar'),('varchar(50)','MyBiggervarchar'),('datetime','Mydatetime')


-- build a string
DECLARE @dynamicColumn NVARCHAR(MAX) = ''

SELECT @dynamicColumn = @dynamicColumn + ', ' + fieldName + ' ' + fieldSize FROM #DBTempSyncScenarioFD

SET @dynamicColumn = 'CREATE TABLE X ( ' + SUBSTRING(@dynamicColumn,2,LEN(@dynamicColumn)) + ' )'

SELECT @dynamicColumn
EXEC SP_EXECUTESQL @dynamicColumn


SELECT * FROM X

I am not sure of the exact problem you are trying to solve, but if you post the problem- it might be easier to help.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-21 : 12:41:30
quote:
Originally posted by James K

Instead of thinking about HOW you want to accomplish something, think about WHAT you want to accomplish.

Ye old XY Problem :)

http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-21 : 17:44:01
Ok so substring it and len it.
Will keep that in mind
Thanks.
Go to Top of Page
   

- Advertisement -