When you think about SQL, you should try to get out of the habit of looping. Loops are generally a lot slower in SQL than they are in other languages. SQL is highly optimized for set based operation.
Your code (in the first block) will compile and run, but it will not generate the results you expect. In words, what you want is... When the value in the column is NULL, return an empty string, otherwise return the value in the column. In SQL, you cannot compare anything to NULL. Think of NULL as "Unknown". You can determine if a value is null by using the IS keyword, like this:
[tt],CASE WHEN [PN1] [!]IS[/!] NULL THEN ' ' ELSE [PN1] END[/tt]
There is a simpler way to write this, too.
[tt], Coalesce([PN1], '') As [PN1][/tt]
The Coalesce function will return the first parameter in the list of parameters that is not null. So, if PN1 is not null, it will be returned by the coalesce function. if PN1 is null, then it moves on to the second parameter (which is an empty string) and returns that instead.
What I have is 20 full statements of the exact same code, just incrementing the part number. Seems there has to be a more efficient way.
If you have a table with 20 columns representing part numbers, you're doing it wrong. I would strongly encourage you to read a couple articles about [google]database normalization[/google]. The better way to store this data is to have a separate table for the parts. Each part would get it's own row in this other table. By having a separate table, you will likely reduce storage space because you are not storing empty data (when you have a widget with just 2 part numbers), and you will not be limited to 20 parts (per widget).
I could show you how to loop. I do use looping in my own code, but I use it sparingly. I have approximately 2000 stored procedures in the database, and I would bet that less than 20 have loops in them.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
