nest dynamic case when statements in sql 2008

Question

Hello Experts,

I have the following code in .net.

foreach (string key in EntityNumbers.Keys)
{

                    if (EntityNumbers[key] != null)
                    {
                        e = new WebServices.Entity();
                        e.HierarchyLevel = key;
                        e.Number = EntityNumbers[key];
                        entities.Add(e);
                    }
                }

This has to convert into SQL in the following format.

declare @HierarchyLevelValue int, @HierarchyLevel nvarchar;

SELECT * from table where 
CASE 
    WHEN @HierarchyLevel='COMPANY' THEN COMPANYNUMBER
    WHEN @HierarchyLevel='BRANCH' THEN BRANCHNUMBER
    WHEN @HierarchyLevel='BUSINESS' THEN BUSINESSNUMBER
END =@HierarchyLevelValue

Above code will work for single entity, but how do I achieve multiple like

    SELECT * from table where COMPANYNUMBER=100 AND BRANCHNUMBER= 1
 AND BUSINESSNUMBER= 1

I can form a final query as a string and execute via ExecuteSQL. Is there any method which will avoid forming string ?

Thanks in advance.

Answer 1

i think you'd be better off using dynamic sql. http://www.kodyaz.com/articles/tsql-sp_executesql-with-output-parameters.aspx

jsut be wary of the dangers of dynamic sql.