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 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 implement paging please? - DataReader Object

Hi, I am a beginner in ASP.NET 2.0

I am creating a simple blog website.

I am using a DataReader object to collect all of the entries in the database (in one full sweep) and then display them by adding rows to a table and populating them.

I would like to enable paging so that only 5 blog entries at a time are displayed. The user should be able to page backwards and forwards 5 at a time.

Please can somebody give me some ideas of how I should do this?

Should I read all of the data into some kind of list so that all of the data is then readily available? An arraylist or something?

I realise that the DataReader object reads in one continuous stream so I couldn't expect it to read backwards and forwards from the database.

I include a portion of my code below which reads the database.

Many thanks.

Dim objCommand As New SqlCommand
objCommand.Connection = objConnection
objConnection.Open()
objCommand.CommandText = "SELECT BlogID, Date, Blog FROM BlogSite2_Blogs ORDER BY Date DESC"

Dim objReader As SqlDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

Dim dateDateTime As Date
Dim strBlogEntry As String
Dim intBlogID As Integer

Do While objReader.Read()
intBlogID = objReader.GetValue(0)
dateDateTime = objReader.GetDateTime(1)
strBlogEntry = objReader.GetString(2)

CreateBlogRow(intBlogID, dateDateTime, strBlogEntry)
Loop

objReader.Close()

check this link

http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

|||

Hi

I would do it in Database with stored procedure.

In the SP described bellow you Just need to pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset.

CREATE PROCEDURE ReturnPage(@.Select varchar(1000), @.OrderByvarchar(1000), @.StartRowint, @.EndRowint)ASBEGIN declare @.ColListvarchar(2000);declare @.Where varchar(2000);declare @.iint;declare @.i2int;declare @.tmpvarchar(1000);declare @.decvarchar(1000);declare @.fvarchar(100);declare @.dvarchar(100);declare @.Symbolchar(2);declare @.SQLvarchar(5000);declare @.Sortvarchar(1000);set @.Sort = @.OrderBy +', 'set @.dec =''set @.Where =''set @.SQL =''set @.i = charindex(',' , @.Sort)while @.i != 0begin set @.tmp =left(@.Sort,@.i-1)set @.i2 = charindex(' ', @.tmp)set @.f =ltrim(rtrim(left(@.tmp,@.i2-1)))set @.d =ltrim(rtrim(substring(@.tmp,@.i2+1,100)))set @.Sort =rtrim(ltrim(substring(@.Sort,@.i+1,100)))set @.i = charindex(',', @.Sort)set @.symbol =casewhen @.d ='ASC'then'>'else'<'end +casewhen @.i=0then'='else''end set @.dec = @.dec +'declare @.' + @.f +' sql_variant; 'set @.ColList =isnull(replace(replace(@.colList,'>','='),'<','=') +' and ','') + @.f + @.Symbol +' @.' + @.fset @.Where = @.Where +' OR (' + @.ColList +') 'set @.SQL = @.SQL +', @.' + @.f +'= ' + @.fend set @.SQL = @.dec +' ' +'SET ROWCOUNT ' +convert(varchar(10), @.StartRow) +'; ' +'SELECT ' +substring(@.SQL,3,7000) +' from (' + @.Select +') a ORDER BY ' + @.OrderBy +'; ' +'SET ROWCOUNT ' +convert(varchar(10), 1 + @.EndRow - @.StartRow) +'; ' +'select * from (' + @.Select +') a WHERE ' +substring(@.Where,4,7000) +' ORDER BY ' + @.OrderBy +'; SET ROWCOUNT 0;'exec(@.SQL)END

Pls refer toEfficient and DYNAMIC Server-Side Paging with T-SQLfor details.

|||

Thank you very much indeed for this Young Fang. I don't understand it all but I shall have a good read and no doubt, I shall learn a lot of new things :)

How do I get the actual name of a column or table in a sql select statement?

Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

RobertYou might try it this way:

To get table names:

SELECT TABLE_NAME FROM Information_Schema.tables

Of course, that gives you some of those default tables as well like sysconstraints and dtproperties and I'm not sure how to get rid of those without hardcoding it into the query.

To get the Columns:

SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

Hope that helps,
-Ian|||sp_Columns @.TableName afaik|||Well the tips you guys came up with worked really well.

Thanks for the help guys.

Basically I took Ian's idea and played with it in SQL Server's Enterprise Manager.

I found that if you say:
SELECT * FROM Information_Schema.tables

instead of:
SELECT TABLE_NAME FROM Information_Schema.tables

you can see all the information available from information_schema.tables

When I looked at the Table_Type column, I noticed that a value of "BASE TABLE" gives you all the user tables plus dtproperties. So I made my where statement filter out the table name dtproperties and keep only the base tables table type.

here is the final sql statement I used for getting the table names:
SELECT TABLE_NAME
FROM Information_Schema.tables
WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> 'dtproperties')

Also, Ian's columns idea:
SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

worked great without having to tweak it at all.

once again thanks for the help

Robert|||I still think sp_tables is better. It's a procedure with all the execution paths compiled. too all their own though.
----

sp_tables
Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

Syntax
sp_tables [ [ @.table_name = ] 'name' ]
[ , [ @.table_owner = ] 'owner' ]
[ , [ @.table_qualifier = ] 'qualifier' ]
[ , [ @.table_type = ] "type" ]

Arguments
[@.table_name =] 'name'

Is the table used to return catalog information. name is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported.

[@.table_owner =] 'owner'

Is the table owner of the table used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server?, if the current user owns a table with the specified name, the columns of that table are returned. If the owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the columns of that table are returned.

[@.table_qualifier =] 'qualifier'

Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

[,[@.table_type =] "'type'"]

Is a list of values, separated by commas, that gives information about all tables of the table type(s) specified, including TABLE, SYSTEM TABLE, and VIEW. type is varchar(100), with a default of NULL.

Note Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be uppercase. If SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the entire parameter must be surrounded by single quotation marks.

Return Code Values
-----
A. Return a list of objects that can be queried in the current environment
EXEC sp_tables

B. Return information about the syscolumns table in the Company database
EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"
-----|||kragie,

I agree about using Stored Procedures as much as possible, especially if you don't have to code it yourself. There are so many benefits: speed, security, reusability, etc...

The reason why I didn't use the sp_tables procedure was because I wanted to exclude a couple tables (dtproperties and a settings table) that are considered to be user tables from being put in the dropdownlist on the web page. The only way i could figure out a way to do this was to write the select statement manually.

I plan on writing a custom stored procedure that will either use the custom select statement or further filter the results of the sp_tables procedure to get the data. That way i get a speed boost and more flexibility with what data I'm playing with.

This morning I was just trying to figure out if any of this stuff is even possible, now that I know it is I plan on refining the solution so it isn't inefficient.

Once again thanks for the suggestions!

BTW, your signature is hilarious. I couldn't stop laughing for 15 seconds.

How do I get SQL Server 2000 Personal Edition

Does SQL Server 2000 Personal Edition install from the SQL Server 2000
Standard CD? I see on the Microsoft website that licensing SQL Server 2000
Standard entitles users to SQL Server 2000 Personal but I don't see an
option for installing Personal. Is it a separate CD?David,
I don't have the CD here to confirm. However, I believe it's on the CD under
Personal/MSDE folder.
--
-oj
http://www.rac4sql.net
"David Morrison" <me@.nospam.com> wrote in message
news:unXLtoG5DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Does SQL Server 2000 Personal Edition install from the SQL Server 2000
> Standard CD? I see on the Microsoft website that licensing SQL Server
2000
> Standard entitles users to SQL Server 2000 Personal but I don't see an
> option for installing Personal. Is it a separate CD?
>|||it came on a separate CD purchased in 2001, don't know how it is now.
"David Morrison" <me@.nospam.com> wrote in message
news:unXLtoG5DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Does SQL Server 2000 Personal Edition install from the SQL Server 2000
> Standard CD? I see on the Microsoft website that licensing SQL Server
2000
> Standard entitles users to SQL Server 2000 Personal but I don't see an
> option for installing Personal. Is it a separate CD?
>

How do I get SQL Server 2000 Personal Edition

Does SQL Server 2000 Personal Edition install from the SQL Server 2000
Standard CD? I see on the Microsoft website that licensing SQL Server 2000
Standard entitles users to SQL Server 2000 Personal but I don't see an
option for installing Personal. Is it a separate CD?David,
I don't have the CD here to confirm. However, I believe it's on the CD under
Personal/MSDE folder.
-oj
http://www.rac4sql.net
"David Morrison" <me@.nospam.com> wrote in message
news:unXLtoG5DHA.1948@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Does SQL Server 2000 Personal Edition install from the SQL Server 2000
> Standard CD? I see on the Microsoft website that licensing SQL Server

---

2000

quote:

---

> Standard entitles users to SQL Server 2000 Personal but I don't see an
> option for installing Personal. Is it a separate CD?
>

---

|||it came on a separate CD purchased in 2001, don't know how it is now.
"David Morrison" <me@.nospam.com> wrote in message
news:unXLtoG5DHA.1948@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Does SQL Server 2000 Personal Edition install from the SQL Server 2000
> Standard CD? I see on the Microsoft website that licensing SQL Server

---

2000

quote:

---

> Standard entitles users to SQL Server 2000 Personal but I don't see an
> option for installing Personal. Is it a separate CD?
>

---

How do I get a RS report onto an Internet website?

I've written our first Reporting Services report, using SQL Server 2005. Since we've never used RS before, we may, or may not, have done some things correctly. One of the things is that we only have the Reporting Services, and Report Manager, on our SQL Server 2005 server, which is in our network behind our firewall.

We also have an ASP.NET 1.1 application which is used by external users (other businesses that do business with us). Because of the nature of our business, we don't want people from one agency to access data from another agency. Since calling the report in RS involved passing parameters to the report in the URL, I decided that I would hide those details by putting the web page returned by RS into an IFRAME in an aspx page. It works just fine in our network, but I discovered that it does not work at all outside of our network.

So, my question is how do I make the RS report available to users outside of our network, in such a way that they don't have access to the parameters that I pass to the report? Should I discard the whole IFRAME approach? I have been told that it will likely require doing something about passing credentials to the backend server from the ASP.NET app, but I don't know how to do that, nor do I know what would be involved.

The most straightforward approach is to use the VS reportviewer control & run your report in local mode, e.g., do not rely upon the report server. This approach eliminates the need to create a security extesion for report server. Cheers!|||

I'm sorry to be so obtuse, but what do you mean by, "run [my] report in local mode"?

|||

DoctorWho:

I'm sorry to be so obtuse, but what do you mean by, "run [my] report in local mode"?

Never mind, I've found it (local mode).