CODEWITHSUNDEEP
sqlsql-servert-sqlprocedure
Inceddy
Inceddy

Reputation: 760

Create SQL procedures in while-loop with procedure-name as variable

is there a way to create a list of procedures via a while-loop in SQL?

What im trying to do is something like this, but with a much bigger attribute-table.

Declare @attributes Table (attrkey text, attr_key text)
  Insert @attributes(attrkey, attr_key) values('userFirstName',  'user_first_name')
  Insert @attributes(attrkey, attr_key) values('userFamilyName', 'user_family_name')
  Insert @attributes(attrkey, attr_key) values('userNameExtra',  'user_name_extra')

Declare @attrkey nvarchar(128)
Declare @attr_key nvarchar(128)
Declare @proc_name_set nvarchar(128)
Declare @proc_name_del nvarchar(128)


While exists (Select * From @attributes)
  Begin
    Select @attrkey = attrkey, @attr_key = attr_key from @attributes
    Set @proc_name_set = 'set_web_user_' + @attr_key
    Set @proc_name_del = 'del_web_user_' + @attr_key

    CREATE PROCEDURE [dbo].[@proc_name_set] @keyval int, @value nvarchar(255)  AS
    UPDATE web_user SET @attrkey=@value WHERE id=@keyval;
    Go
    CREATE PROCEDURE [dbo].[@proc_name_del] @keyval int, @value nvarchar(255)  AS
    UPDATE web_user SET @attrkey=NULL WHERE id=@keyval AND @attrkey=@value;
    Go

    Delete from @attributes where attrkey like @attrkey
  End

Upvotes: 1

Views: 252

Answers (1)

jpw
jpw

Reputation: 44871

While there might be better ways of doing this I guess it might be a one time operation and performance might not matter much. If so then this modified code should work and create a set_web_user_ and del_web_user_ procedure for all attributes:

Declare @attributes Table (attrkey text, attr_key text)
Insert @attributes(attrkey, attr_key) values('userFirstName',  'user_first_name')
Insert @attributes(attrkey, attr_key) values('userFamilyName', 'user_family_name')
Insert @attributes(attrkey, attr_key) values('userNameExtra',  'user_name_extra')

Declare @attrkey nvarchar(128)
Declare @attr_key nvarchar(128)
Declare @proc_name_set nvarchar(128)
Declare @proc_name_del nvarchar(128)
Declare @proc_text nvarchar(max)

While exists (Select * From @attributes)
  Begin
    Select @attrkey = attrkey, @attr_key = attr_key from @attributes
    Set @proc_name_set = 'set_web_user_' + @attr_key
    Set @proc_name_del = 'del_web_user_' + @attr_key

    Set @proc_text = N'CREATE PROCEDURE [dbo].[' + @proc_name_set + '] @keyval int, @value nvarchar(255)  AS
    UPDATE web_user SET ' + @attrkey + ' = @value WHERE id = @keyval;'

    EXEC sp_executesql @proc_text

    Set @proc_text = N'CREATE PROCEDURE [dbo].[' + @proc_name_del + '] @keyval int, @value nvarchar(255)  AS
    UPDATE web_user SET ' + @attrkey + ' = NULL WHERE id = @keyval AND ' + @attrkey + ' = @value;'

    EXEC sp_executesql @proc_text

    Delete from @attributes where attrkey like @attrkey
  End

Upvotes: 1

Related Questions