SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop through rows?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sapator
Constraint Violating Yak Guru

Greece
384 Posts

Posted - 07/18/2014 :  12:21:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/18/2014 :  13:26:41  Show Profile  Reply with Quote
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
Posting Yak Master

172 Posts

Posted - 07/18/2014 :  13:55:32  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
411 Posts

Posted - 07/18/2014 :  16:25:40  Show Profile  Reply with Quote
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

Greece
384 Posts

Posted - 07/20/2014 :  18:34:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/21/2014 :  08:40:09  Show Profile  Reply with Quote
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

Greece
384 Posts

Posted - 07/21/2014 :  08:50:40  Show Profile  Reply with Quote
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;


Edited by - sapator on 07/21/2014 08:51:59
Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 07/21/2014 :  11:01:01  Show Profile  Reply with Quote
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

Greece
384 Posts

Posted - 07/21/2014 :  11:47:13  Show Profile  Reply with Quote
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
Posting Yak Master

172 Posts

Posted - 07/21/2014 :  12:38:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/21/2014 :  12:41:30  Show Profile  Reply with Quote
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

Greece
384 Posts

Posted - 07/21/2014 :  17:44:01  Show Profile  Reply with Quote
Ok so substring it and len it.
Will keep that in mind
Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000