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

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

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

Sounds like a job for an OUTER JOIN:
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

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

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