locked
Create a stored procedure from a dynamic query RRS feed

  • Question

  • User1839056048 posted

    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

    Sunday, February 21, 2016 6:44 AM

Answers

  • User-219423983 posted

    Hi klbaiju,

    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.

    According to your need description, I think you’d better have a look at the following thread which provides a method about dynamically building the SQL query string.

    http://stackoverflow.com/questions/12846743/dynamic-update-statement-with-variable-column-names

    For your need, you could make the SP have two parameters, one for the “uname” and the second one for the “columnName” and the below example code you could have a look and modify it according to your needs.

    declare @sql nvarchar (1000);
    declare @column_name varchar(20)
    declare @uname  varchar(20)
    set @sql = N'update [dbo].[admin] set ' + @column_name + '= ''1'' where [Name] = '''+@uname +''''
    
    exec sp_executesql @sql 
    

    When you want to modify multiple rows with different “uame”, you could call this SP repeatedly with different column name and “uname” in the C# code.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 22, 2016 12:17 PM