Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
| Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 11th, 2003, 10:42 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default looping through return records in sproc

Hi,

I am getting an error from the following code in my sproc because it is possible for hdwCat2 to be NULL. I don't know how to do a loop in stored procedures - how can I loop through this and do a separate select without the cat2 field for those records where cat2 is NULL?

    SELECT i.hardwareID, t.hdwCat1 + '/' + c.hdwCat2 AS hdwType, i.hdwDescr
    FROM tblHdwInventory i
    JOIN tblHdwCat1 t ON i.hdwCat1 = t.hdwCat1ID
    JOIN tblHdwCat2 c ON i.hdwCat2 = c.hdwCat2ID
    JOIN tblLocationHistory h ON i.hardwareID = h.hdwID
    WHERE h.facilityID = @clinicID
    AND hdwOrSys = 0

Thank you,

John
 
Old July 11th, 2003, 01:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

The concept of 'loop' doesn't really apply to SQL. All the rows in a query are operated on as a set in its entirety. That is, the query operates as if all the rows are operated on simultaneously and all at once. Now obviously it doesn't really do that, but it is helpful to think of query operations this way.

The SQL Server programming language T-SQL does implement a looping construct via the WHILE statement (or, ick, a GOTO statement). The statements within the scope of a WHILE loop are other T-SQL statements or entire queries, though - so see above.

If you want to operate on the rows of a query one row at a time, you can do so by using a cursor. Cursors have their place, but they are almost never needed, as they are slow and are resource hogs, and a far (i.e. order of magnitude) more effcient set-based solution almost always exists, although it can sometimes be hard to find...

What exactly do you want to happen when your 'hdwCat2' column is null? It appears that this named column appears in both the 'tblHdwInventory' table and the 'tblHdwCat2' table. Can it be NULL in both tables? What does it mean for this column to have a NULL value in either or both tables? What do you mean by 'separate select'? What exact error message are you getting?

Perhaps if you explained exactly what you are trying to do, and give some insight into your table structures, we can help...

P.S. I see you have described your structures in another post. See my comments there regarding your JOIN question, but my other questions above still stand.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old July 14th, 2003, 09:02 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, the result of this sproc is to list all hardware assigned to a particular facility. Each piece of hardware (tblHdwInventory) has a Type (hdwCat1) and Category (hdwCat2). Type is required while Category can be left NULL. So, when I ran my sproc below I noticed that all hardware items except those with hdwCat2=NULL were returned. This is because the join to tblHdwCat2 doesn't work when the linking field in tblHdwInventory is NULL. I need them all to be returned, so I was thinking of doing some sort of "IF" statement - if hdwCat2 from tblHdwInventory for a particular record is not null then run the sproc with the code below; if hdwCat2 is null, then run the code below minus the tblHdwCat2 JOIN and the c.hdwCat2 return value. I would need to "loop" through each value to check this condition - does this make sense? There must be a fairly easy way to do this, I think I am bogged down in the details at this point.

Any advice is welcome and appreciated. The other thoughts I had were to either not return the Category value or to make hdwCat2 required (no nulls) in my table.

Thanks!

John
 
Old July 14th, 2003, 09:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sounds like a job for an OUTER JOIN:
Code:
    SELECT i.hardwareID, t.hdwCat1 + COALESCE('/' + c.hdwCat2,'') AS hdwType, i.hdwDescr
    FROM tblHdwInventory i
    JOIN tblHdwCat1 t ON i.hdwCat1 = t.hdwCat1ID
    JOIN tblLocationHistory h ON i.hardwareID = h.hdwID
    LEFT JOIN tblHdwCat2 c ON i.hdwCat2 = c.hdwCat2ID
    WHERE h.facilityID = @clinicID
    AND hdwOrSys = 0;
Note that we have to fiddle with the 'hdwType' value in the resultset a bit. Since in some cases 'i.hdwCat2' can be null, the OUTER JOIN will result in 'c.hdwCat2' being NULL. Normally, an expression which contains a NULL operand will result in a NULL, so to prevent that we use COALESCE to return a 0 length string to be concatenated to 'hdwCat1' in the NULL case.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old July 14th, 2003, 09:55 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Jeff,

That's exactly what I needed - I haven't used COALESCE before. I am also going to spend some time this week beefing up my knowledge of joins!

Thanks again,

John
 
Old July 14th, 2003, 10:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by jtyson
 I am also going to spend some time this week beefing up my knowledge of joins!
Ask here if you have any questions. I'll be glad to help explain things if I can...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
for-each looping - mystery comma return Navy1991_1 XSLT 6 June 5th, 2008 01:52 PM
Get sproc return value from ObjectDataSource Bob Bedell ASP.NET 2.0 Professional 3 January 19th, 2008 10:22 PM
skipping records while looping stealthdevil Access VBA 3 October 26th, 2006 10:22 AM
Looping through subform records V Access VBA 8 April 20th, 2004 08:10 AM
where is cursor after looping through records? monstermash Classic ASP Basics 7 July 22nd, 2003 03:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.