Create a storedprocedure from a dynamic query

1

Hi,
I want to write a storedprocedure from a query.
following is my working query
string id;
string[] words = id.Split(',');
int count = words.Length;
string c = "update admin set ";
string k = " ";
string s = "";
string l = "where uname IN(";
for (int i = 0; i < count; i++)
{
string[] w=words[i].Split('-');
if (i != count - 1)
{
k = " " + w[1] + "=case when uname='" + w[0] + "' then 1 else " + w[1] + " END,";
s += "'" + w[0] + "'";
s += ',';
}
else
{
k = " " + w[1] + "=case when uname='" + w[0] + "' then 1 else " + w[1] + " END ";
s += "'" + w[0] + "'";
s += ')';
}
c += k;
//s += w[0];

            }
            l += s;
            c += l;

here uname is a field in admin table and it is a general query for n parameters

i want to create stored procedure similar to that of following stored procedure.
it is for only 2 parameters
update admin
set [add]= CASE WHEN uname = 'jain' THEN 1 ELSE [add] END
,[edit] = CASE WHEN uname='baiju' THEN 1 ELSE [edit] END
where uname IN ('jain', 'baiju')

the above storedprocedure is working one and it has only 2 unames. with add and edit.
my requirement has n unames with add,edit,delete,view.
how it is possible.

Regards
Baiju

2

I would handle this differently. I would pass the comma-delimited string as a parameter, and then use a "splitter function" in SQL to convert the delimited-list into a [temporary] table - i.e. one row per delimited value and then I can just JOIN that temporary table with the [admin] table, or process the data in some other way - as a SET rather than as individual rows.

This will be much more efficient than generating dynamic SQL with a large CASE statement or similar.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Scroll down to "Figure 21: The Final "New" Splitter Code, Ready for Testing"