Nested SQL with condition logic

I'm trying to combine two posts I found into one solution but I think I'm going for the impossible.

Here's a shell of the nested select and I've indicated where I'd insert the nested logic.

SELECT node_id, fk_id, nd_src_tbl_name, ( ** conditional code ** )
FROM vNodeCollection as vNc

Here's the nested logic I want to insert for each row:

BEGIN
IF [vNc].nd_src_tbl_name = 'tbl_CorporateRegion'
BEGIN
SELECT cr_textFROM tbl_CorporateRegion WHERE cr_id = [vNc].fk_id
END
ELSE IF [vNc].nd_src_tbl_name = 'tblCountry'
BEGIN
SELECT country_text FROM tbl_Country WHERE country_id = [vNc].fk_id
END
END

I'm trying to be dynamic by storing my table names with my node detail information but I'm starting to realize this may not work well.

Any ideas?No, this will not work. If u want conditional statements in a select, use the CASE functionality. See Books Online for details on how to use it. Alternatively, a user defined function may work as well (if u have SQL Server 2000).|||I forgot about custom functions. After some stumbling with the syntax I was able to make it work. I have no idea if it's efficent though.

Thanks for the idea.

Nested cursor

Is there a better way to nest cursors in SQL Server (I'm using 2005). Due to the nature of the solution a set-based approach will not work. Essentially I have a list of People. Each Person has Multiple Properties, so at the moment I have something along the lines of ...

Declare PersonCursor
Open PersonCursor
Fetch Next Person Cursor
While FetchStatus <> -1
Declare PropertyCursor (for Select according to current values in PersonCursor)
Open PropertyCursor
Fetch Next PropertyCursor
While @.@.FetchStatus <> -1
Process Each Property
Fetch Next PropertyCursor
End
Close PropertyCursor
Deallocate PropertyCursor
End
Close PersonCursor
DeallocatePersonCursor

I hope it's clear. What I'm asking is (and I don't think it's possible) is that is there a method by which I can Declare the PropertyCursor once, but open it multiple times using different values? I haven't used SQL Server cursors much but have seen this done in other RDBMS's.

Even hints on another angle to approach from would be useful. As I say, the steps involved at the "Process Each Property" stage are not really suited to a set-based approach, as I have to maintain counters, and dynamically build SQL unique to each iteration dependent on the counter values.

Any thoughts much appreciated.

Greg.

If the problem is not suited to a set based approach, why use SQL Server? Surely this would be easier to perform at the client side? You could still have the "process each property" code in a stored procedure if you like, but have it called from the client side instead. Can you use a set based appraoch using a CASE statement? CASE statments allow you to apply quite complex logic using a set based approach as it can do something different for each row returned from the query based on the value of that particular row.

HTH

|||We're using SQL Server mainly because

a) it's a heavily DB intensive process, and

b) in this instance, there is no client - the process is a Start of Financial Year billing process

We're using SQL Server stored procs to generate the billing data, and then SSIS to extract it to be printed.

Greg.

|||

Perhaps if you post more information about your specific calculation we can tell you how to do it using a set-based approach.

Oftentimes, this is actually possible (or we, as implementers, want to make that statement true since cursoring even in one level is often not efficient compared to the equivalent set-based query).

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

A solution is to call in your outer cursor loop a sp where you can handle the property cursor.

|||

You can have the cursors nested, without a stored procedure, but you need to declare/open/fetch/close/deallocate the inner cursor within the WHILE loop of the outer cursor.

Good luck, Dean