How do I loop thru a result set?

How do I loop thru a result set Without using a curosr?[1]
select @.key = min(key) from tab
while @.key is not null begin
...
select @.key = min(key) from tab where key > @.key
end

[2]
Define a function which does your dirty work and returns a result value
(even dummy)
Then
select dbo.fn_dirtywork(col1,col2,col3,col4,...) from tab

[3]
Don't.

"Andrew Young" <atyoung75@.yahoo.com> wrote in message
news:422d5636.0406280544.7f205a34@.posting.google.c om...
> How do I loop thru a result set Without using a curosr?|||On 28 Jun 2004 06:44:05 -0700, Andrew Young wrote:

>How do I loop thru a result set Without using a curosr?

Hi Andrew,

Mischa already gave some suggestions. I'd have put number three first (and
repeated it several times).

Seriously: why do you want to loop through a result set? Can you describe
the business need you're trying to solve? Set-based solutions tend to be
better and quicker and most problems CAN be solved with set-based SQL.

On the other hand, if you really must (or want to) loop through a result
set, why not use a cursor? That's SQL's standard instrument for looping
through a result set. Of course, you can find other ways to do the same,
but they'll probably be less efficient than a cursor (which is already
very inefficient!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Andrew Young (atyoung75@.yahoo.com) writes:
> How do I loop thru a result set Without using a curosr?

I could a few more suggestions to Mischa's list, but I think I go with
his [3]. Then again, you should probably better clarify what your
real problem is. Maybe you are not iterating on the server, but in a
client?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is my process:

I am pulling performance metrics for over a thousand servers, W2K,
W2K3, NT, RS6000, and AS400. This equates to about 50 to 100 million
rows of data in a 24 hour period. I have a SLA of 24 hours to provide
this data on a daily basis.

My process consists of pulling a comma delimited format file that I
'bulk insert', from a UNC path, into a temporary table. I then fire a
stored proc that reads thru the temp table and process the data.

The data file format is:
hostname,application,application_instance,paramete r,timesatmp,data_point

My database consists of a hostname table, application table, instance
table, parameter table, agregatted data table and a raw data table.

All have primary key and constraints except for raw data table. My
first stored proc determines the existence of the hostname, app,
inst,param, and if they don't exist it adds them to the respected
table and then dumps the raw into the raw data table with application
and parameter keys to be used later. This proc will fire and process
several time in a minute processing inbound data files.

Every six hours a aggregation proc fires and move the data from the
raw table into the agg table and then deletes the raw stuff.

I hope this explains my need for looping. Any advise would be much
appreciated.|||Andrew Young (atyoung75@.yahoo.com) writes:
> My process consists of pulling a comma delimited format file that I
> 'bulk insert', from a UNC path, into a temporary table. I then fire a
> stored proc that reads thru the temp table and process the data.
> The data file format is:
> hostname,application,application_instance,paramete r,timesatmp,data_point
> My database consists of a hostname table, application table, instance
> table, parameter table, agregatted data table and a raw data table.
> All have primary key and constraints except for raw data table. My
> first stored proc determines the existence of the hostname, app,
> inst,param, and if they don't exist it adds them to the respected
> table and then dumps the raw into the raw data table with application
> and parameter keys to be used later. This proc will fire and process
> several time in a minute processing inbound data files.
> Every six hours a aggregation proc fires and move the data from the
> raw table into the agg table and then deletes the raw stuff.
> I hope this explains my need for looping.

Not really. That is, I don't really see where the loop comes in.

When you get that data in, you would do

INSERT hostnames (hostname)
SELECT DISTINCT hostname
FROM rawdata r
WHERE NOT EXISTS (SELECT *
FROM hostname h
WHERE r.hostname = h.hostname)

and the similar for the other tables.

The aggregation should be possible to carry out with set-based statements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How do I loop thru a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

SELECT id, label, url, sort
FROM mainNav
ORDER BY sort

Set @.tc = @.@.rowcount

while @.i <= @.tc

begin
Set @.i = (@.i + 1)

/* Select for submenu items*/
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end

RETURNI'm thinking that what you really want to do is perform a join and then handle the presentation stuff on the client side. I can't think of any valid reason why you would want to write your stored proc in the manner you have outlined above.

SELECT
m.id as MainID,
m.label as MainLabel,
m.url as MainUrl,
m.sort as MainSort,
s.id,
s.label,
s.url,
s.sort
FROM
mainNav m inner join SubNav s
ORDER BY
m.sort,
s.sort

Regards,

hmscott|||Yeah, you definitely seem hazy on the SQL concept. What is the output format you want for your dropdown list? Give us a sample of the data you want to display.|||The objective is to return a .Net dataset that contains a series of recordsets . The first recordset is the items contained in the top bar of the menu site. The subsequest recordsets contain the submenus for each item in the top menu.

The challenge is that it's not a simple binding issue once the data is returned from the stored proc. Some items in the top bar may not have a submenu and therefore require different html and javascript.

Inorder to render a lightweight, css-based (and W3C compliant) menu system, I need to determine at runtime which items have submenus and which don't

- items with no submenu need to have code that only closes other menus
- items with submenus need to have the closing code and also code to open there respective submenu.

A working (static) example would be something like http://peelcas.org/home/index.aspx

In order to pull this off, I have a c# routine that loops thru the dataset and renders the html.

Here's the c# code

///////////////////////////
// build global menu system
///////////////////////////

string sqlConnstring = ConfigurationManager.ConnectionStrings["sqlConnString"].ConnectionString;
DataSet NavData = new DataSet();
NavData = SqlHelper.ExecuteDataset(sqlConnstring, CommandType.StoredProcedure, "OPA_GetMenuItems");
//Response.Write(NavData.Tables.Count.ToString());

int i = 1; // counter for looping thru tables collection
int tc = NavData.Tables.Count - 1;
System.Text.StringBuilder sbNavLinks = new StringBuilder();
System.Text.StringBuilder sbSubLinks = new StringBuilder();
string url;
//string webSectionName;
string label;
string anchorId;
string menuId;

// build main nav bar
sbNavLinks.Append("<div id=\"navBar\"><ul>\r");
while (i < tc)
{
label = NavData.Tables[0].Rows[i].ItemArray[1].ToString();
anchorId = NavData.Tables[0].Rows[i].ItemArray[3].ToString();
url = NavData.Tables[0].Rows[i].ItemArray[2].ToString();

// no submenu items for this web section therefore...
// ...create main nav bar link with global menu closing javascript only
if (NavData.Tables[i].Rows.Count == 0)
{
sbNavLinks.Append("<li><a href=\""
+ url + "\" onmouseover=\"P7_autoLayers(0);\">"
+ label + "</a></li>\r");
}
// has submenu items therefore...
// ...create main navbar link with submenu opening javascript
else
{
// main navbar link
sbNavLinks.Append("<li><a href=\""
+ url + "\""
+ " id=\"Anchor" + anchorId + "\""
+ " onmouseover=\"P7_autoLayers(0,'subMenu" + anchorId + "');"
+ "P7_Snap('Anchor" + anchorId + "','subMenu" + anchorId + "',0,24);"
+ "\">"
+ label + "</a></li>\r");
}
i++;
}
sbNavLinks.Append("</ul></div>\r");
navBar.Text = sbNavLinks.ToString();

// build submenus

int j = 0; // counter for looping thru rows in current table
int rc; // row count of current table
i = 1;
while (i < tc)
{
if (NavData.Tables[i].Rows.Count > 0)
{
// extract menu id for use in div id

menuId = NavData.Tables[i].Rows[0].ItemArray[4].ToString();
//Response.Write(webSectionSort);

sbSubLinks.Append("<div id=\"subMenu" + menuId + "\" style=\"position:absolute; z-index:" + menuId + "; visibility: hidden;\">\r");
sbSubLinks.Append(" <div class=\"subButton\">\r");
sbSubLinks.Append(" <ul>\r");

rc = NavData.Tables[i].Rows.Count -1;

while (j <= rc)
{
// extract data for this link
label = NavData.Tables[i].Rows[j].ItemArray[1].ToString();
url = NavData.Tables[i].Rows[j].ItemArray[0].ToString();

sbSubLinks.Append("<li><a href=\""
+ url + "\" class=\"subButton\">"
+ label + "</a></li>\r");
j++;
}

sbSubLinks.Append("</ul></div></div>\r\r");
}
i++;
j = 0;
}
subNavBar.Text = sbSubLinks.ToString();
NavData.Dispose();

Yes I too dislike all this looping business, but I don't have a way of binding and achieving the desired results...|||What about a left join, instead of an inner join. With a left join, you could easily determine the MainMenu items with no submenus (the submenu fields would be null).

You would still loop through it on the web server.

Alternatively, you could use the FOR XML clause to create an XML string (which you would have to wrap inside of xml tags to make compliant).

Regards,

hmscott|||I'm not a C coder. You need to give us the layout of the recordset that you want to get from the server. We can help you with that, and from there on its up to you.|||Thanks for the offer...

I think hmscott's suggestion of a Left Join might just work, so I will groove on that for a while. As much as you're not a C# guy, I'm not a Sql guy, so I often just need a nudge in the right directlon..

Thanks againsql

How do I loop through the records in a temporary table

How do I loop through the records in a temporary table?
ThanksHi Tim
Processing rows is usually less efficent than using set based commands on
your data set, therefore if you can use a set based solution is should be
better. You can use a CURSOR on a temporary table for example:
SELECT LastName, FirstName
INTO #Employees
FROM Northwind.dbo.Employees
WHERE LastName like 'B%'
DECLARE @.LastName [nvarchar] (20) ,
@.FirstName [nvarchar] (10)
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName
FROM #Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @.LastName, @.FirstName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.FirstName + N' ' + @.LastName
FETCH NEXT FROM Employee_Cursor INTO @.LastName, @.FirstName
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
DROP TABLE #Employees
Check out information on DECLARING/OPENING/CLOSING/DEALLOCATING and FETCHING
from cursors in Books Online
John
"Tim Kelley" wrote:
> How do I loop through the records in a temporary table?
> Thanks
>
>

How do I loop through dataset then replace?

How would I loop through the rows of a dataset, then replace certain character in a certain column?

I have a database which has a date field formatted 23/08/2007, I wish to loop through the dataset containing the results from the dataset, change the format of the date to 23.08.2007 then store the value back into the dataSet, which is called 'dataSet', and the table is called 'News'.

Using C# by the way.

Thanks in advance

I suggest you to do this in database level though stored procedure that is dynamic (receive your changing criteria via input parameters).
Looping in stored procedures can be done though cursor (I know it is not recomended to use cursor in database for performance issue, but I think in this case it will be much better than looping into your application).

Tip:
You can get many date format for your DATETIME column in database. For example try: Convert(varchar(25), MyDateTimeColumn,113) or Convert(varchar(25), MyDateTimeColumn,111) [Used SQL Books Online for more samples and examples].

Good luck.

|||

Thanks

I'm new to this, so I'll read up on stored procedures, infact, I'm sure I have a whole book on SQL somewhere.

|||

While I read up on stored procedures can someone tell me how to do this in the application? The dataset will only contain 3 rows so it shouldnt have too much of an effect on the application.

|||

If it is just 3 records, then it will not affect the performance that much!

Here is the idea:
Let yourSqlDataAdapterfillyourDataSet.
Now, you have the data in the DataSet, loop into those records in DataSet.
Use String.Replace for replacing issue you have.

http://msdn2.microsoft.com/en-us/library/system.string.replace(VS.71).aspx

Good luck.

|||

What I wanted to do was get data from a database in date order, then alter the format of the date once it was in the dataset.

Instead of faffing about with it, I simply set the date in the database twice, i.e. Date, then a display date.

Now the data is sorted using the Date, but it's the displayDate field which displays the date to the user.

Thanks for all the help

How do I loop through a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause usesnot the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDUREdbo.OPA_GetMenuItems
AS
Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = @.i)
ORDER BYmainNavId, sort
end

RETURN

Here's one way: You could get the resultset into a table variable. Add an additional column in the table variable and mark it off as processed after each record.

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

DECLARE @.t tabke (id int, label varchar(100), url varchar(100), sort varchar(100), Processed char(1) )
DECLARE @.minid int

INSERT INTO @.t
SELECT
id, label, url, sort, 'N'
FROM
mainNav
ORDER BY
sort

WHILE EXISTS (SELECT 1 FROM @.t WHERE Processed = 'N')
BEGIN
--Get the first record
SELECT
@.minid = id
FROM
@.t
WHERE
Processed = 'N'
ORDER BY
id

--your processing code
/*
Select for submenu items
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end
*/

--make sure you mark the record as Processed
UPDATE
@.t
SET
Processed = 'Y'
WHERE
id = @.minid

END

|||

Depends on if you want the items to come back in one or multiple recordsets.

Here's a single recordset:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav
UNION
SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's two recordsets:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav

SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's multiple recordsets:

Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = (SELECT TOP 1 FROM (SELECT TOP @.i id,sort FROM mainNav ORDER BY sort,id) ORDER BY sort DESC,id DESC))
ORDER BYsort
end

You can also do it using a cursor if you want, which would probably be easier/faster if you have a LOT of menu items, but I'm guessing that since it's a menu, you are only talking about 5-20 items and not thousands.

|||

Basic cursor logic works like this, but the syntax is NOT correct, I haven't used a cursor in a LONG time:

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @.id
WHILE (@.@.FETCHSTATUS<>-2)
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav WHEREmainNavID=@.id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @.id
END
CLOSE MyCursor
DEALLOCATE MyCursor

That should be pretty close, all except for the READ NEXT FROM... I forget the command/syntax, that definately is not correct. And the cursor declaration is a bit off, but close.

|||

The multiple recordsets approach is the one I'm after - I need an .Net data to spit out the menus back in C#. I tryed your example and it did not compile - Sql came back with:

Incorrect Syntax near keyword 'From'.
Line 24: incorrect syntax:near'@.i'.

any ideas?

|||Ah, you must be using SQL Server 2000, yeah, that's not valid on 2000, only 2005. I'm not sure what your code looks like, but I would do the single resultset approach. Just loop through the resultset, and whatever you would do when you get a new resultset, just do it when the mainNav field changes value.|||

Thanks for your help. I actually took a different direction and managed to get around the problem with a left join...

Thanks again...

How do I lock a table in loop

I'm doing some testing and need to lock a table indef I need to tablock
in a loop until its canceled can't seem to work it out?
thanksred
Please look at TRANSACTION ISOLATION Level in the BOL.
"red" <cap_sch@.yahoo.co.uk> wrote in message
news:1118227150.695525.254310@.z14g2000cwz.googlegroups.com...
> I'm doing some testing and need to lock a table indef I need to tablock
> in a loop until its canceled can't seem to work it out?
> thanks
>sql

How do I lock a table in loop

I'm doing some testing and need to lock a table indef I need to tablock
in a loop until its canceled can't seem to work it out?
thanks
red
Please look at TRANSACTION ISOLATION Level in the BOL.
"red" <cap_sch@.yahoo.co.uk> wrote in message
news:1118227150.695525.254310@.z14g2000cwz.googlegr oups.com...
> I'm doing some testing and need to lock a table indef I need to tablock
> in a loop until its canceled can't seem to work it out?
> thanks
>

How do I lock a table in loop

I'm doing some testing and need to lock a table indef I need to tablock
in a loop until its canceled can't seem to work it out?
thanksred
Please look at TRANSACTION ISOLATION Level in the BOL.
"red" <cap_sch@.yahoo.co.uk> wrote in message
news:1118227150.695525.254310@.z14g2000cwz.googlegroups.com...
> I'm doing some testing and need to lock a table indef I need to tablock
> in a loop until its canceled can't seem to work it out?
> thanks
>

how do I just stop a flow and then make it go into the next iteration?

Hey guys, wonder if you could help.

I have a flow which is within a foreach loop. The first box in the flow is a scrip component which makes some check in a database.

Is there a way to say, inside that script component: 'stop the flow here, don't bother going onto the next box, and go on to the next iteration'?

thanks!

andy

Not tried this but how about:

Have a variable in the package.

Increment in the loop.

Check the value in the script and proceed if necessary.

|||

In your script, set a variable, and evaluate that avraible with an expression on the workflow.

Example - http://www.sqlis.com/306.aspx

sql