February 21, 2016 at 4:13 am
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.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
February 22, 2016 at 6:50 am
It would be helpful if you would provide the CREATE TABLE statement for your admin table, some INSERT statements to populate it with sample data and the query used to retrieve data into your program that helps you populate id. At minimum, give us some sample values of id.
string[] words = id.Split(',');
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 22, 2016 at 6:51 am
Please don't post multiple questions for the same problem.
http://www.sqlservercentral.com/Forums/Topic1762934-392-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply