Oh man, i very nearly pulled this thing out without CASE and DYNAMIC-SQL...

here is the code; if in future u are planning to append many extra columns, say e,f,g...

Is your .Net Code executing the .sql thro sqlcmd/isql or is it reading the whole database creation query into a string and then executing the string using ExecuteQuery ?

Hmmm..

Tel me if u did this:

1. You Right-click on the database, "Create To" to a new SQL Server Window

2. SAve this script as .sql file

3. And now u are trying...

For SQL Server 2005, the compatiblity level can maximum be 90 and there is no command like HONOR_BROKER_PRIORITY

I tried this code in both SQL 2005 and 2008. i got error...

One doubt mate, are u trying to run this piece of code in SSMS 2005 or SSMS 2008?

Mate, are u sure we have instr function in SQL Server ? :blink: :blink:

I agree with Lynn here... CASE statements look appropriate here and to my knowledge readable too, then why would you prefer dynamic SQL??

here is a sample code with CASE statement...

I always ask people to look up to your post on forum etiquettes Jeff.. i will add that to my signature as well 🙂

Exactly Jeff!!

When the OP is unclear then we are explicitly type-casted to be unclear 😀 :w00t:

thats the turning point, please be specific with what your requirement...

Thanks Lynn for checking that out!! i was kind of reluctant to post it without testing.. now that u tested and decorated the code, the OP can directly us that.....

hi there, here is the code u asked for.. check the output of this from that print statement.. if that matches your requirement, uncomment the exec part and your requirement...

I understood partially your requirement and here is the code for that

SELECT

CASE LOOKUP.COL1

...

Jeff i did not have the time to execute the query, my bad :doze:.. but the solution using the recursive...

the solution i have provided is created by me closely following the recursive-cte explanation give in BOL 2008 (http://msdn.microsoft.com/en-us/library/ms186243.aspx).. excellent article with the example walk-thro.. u ll find it very...