How to Pass Parameter as a List In a Stored procedure in a dynamic Insert Query

Question

I have a scenario that @PRIVILEGEID will have multiple value and @ROLEID will be same every time for those privilege Id.

So I have to insert the data into table. Below is the Code For Procedure:-

ALTER PROCEDURE [dbo].[ADD_ROLE] (
  @ROLENAME varchar(50),
  @PRIVILEGEID int )
AS
BEGIN

  DECLARE @QUERY varchar(1000);
  DECLARE @ROLEID int;
  SET @ROLEID = ( SELECT Max(ROLEID)
                  FROM ( SELECT
                           Max(CREATED_DATE) AS MAX_DATE,
                           ROLEID
                         FROM ROLE
                         WHERE ROLENAME = @ROLENAME
                               --(ROlename can be changed dynamically, take 'Manager' as example as of now.)     
                           AND CREATED_DATE IS NOT NULL
                         GROUP BY ROLEID ) X );
  --PRINT @ROLEID  

  SET @QUERY = 'INSERT INTO [ROLES_PRIVILEGES]  (ROLEID,PRIVILEGEID) VALUES (''' + Cast(@ROLEID AS varchar) + ''',''' + Cast(@PRIVILEGEID AS varchar) + ''')';

  EXECUTE ( @QUERY );

END;

Now @PRIVILEGEID will have a dynamic list of multiple values for the fixed @ROLEID and My issue is I can pass only one @PRIVILEGEID at one time.

Exec ADD_ROLE 'BACKOFFICE',@PRIVILEGEID @PRIVILEGEID=[2, 3, 4, 5, 6]

-- (How to pass multiple value for PRIVILEGEID )

I have tried While loop also but not sure how to implement it.

Please help.

Check TVP (Table value paramater) or pass comma separated value and use function to convert comma separated values to table. — Harshad Vekariya, Sep 11 '19 at 12:19
That stored procedure doesn't need dynamic SQL. You can write an `INSER ... FROM` query to insert the results of a query into a table. The GROUP BY and `MAX(CREATED_DATE)` aren't needed either. If you want the largest RoleID for a name you can write `SELECT MAX(RoleID) from Role where RoleName=@RoleName and Created_Date IS NOT NULL)` — Panagiotis Kanavos, Sep 11 '19 at 12:30
The entire stored procedure could contain just `INSERT INTO ROLE_PRIVILEGES (RoleID,PrivilegeID) SELECT MAX(RoleID), @PrivilegeID from Role where RoleName=@RoleName and Created_Date IS NOT NULL)` — Panagiotis Kanavos, Sep 11 '19 at 12:34
How will i pass the multiple PRIVILEGEID. and that Privilege Id is not known to us it will be entered by some other user. — som_1522, Sep 11 '19 at 12:36
Check this: https://stackoverflow.com/questions/15585632/how-to-convert-comma-separated-nvarchar-to-table-records-in-sql-server-2005 — Harshad Vekariya, Sep 11 '19 at 12:52
You can change the parameter @PRIVILEGEID to nvarchar(max) and pass the ids as comma separated string. — PeterHe, Sep 11 '19 at 13:30
converting data will not help.. Exec ADD_ROLE 'BACKOFFICE',@PRIVILEGEID @PRIVILEGEID=[2, 3, 4, 5, 6] so it should enumerate the [2, 3, 4, 5, 6] one by one. — som_1522, Sep 11 '19 at 14:43

Doug Coats · Answer 1 · 2019-09-11T14:58:59.117

0

Here is an example where I take a comma delimited string and dump into a table. This process works by converting it into xml and using xmlqry to parse. I like it because I feel its easier to read and looks less convoluted.

Let me know if it works for you.

USE yourDatabase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_parsedelimited]
    (
        @str varchar(max)
    )
AS
BEGIN
    if object_id('tempdb..#Emails') is not null drop table #Emails;
    CREATE TABLE #Emails (Email varchar(2500));
    Declare @x XML;
    select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml);

    INSERT INTO #Emails (Email) 
    select 
        t.value('.', 'varchar(50)') as inVal
    from @x.nodes('/A') as x(t);

    SELECT * FROM #Emails;
    drop table #Emails;
END
GO

edited Sep 11 '19 at 14:58

answered Sep 11 '19 at 14:05

Doug Coats

6,255
9
27
49

Didnt realize one of the comments in the OP was a link to an answer using the same method. – Doug Coats Sep 11 '19 at 14:06
While testing the above procedure of yours exec [_SyncSendGridUnsubscribe] [A,B,C], it is giving error 'Conversion failed when converting the nvarchar value 'A' to data type int'. – som_1522 Sep 11 '19 at 14:39
Also Can you suggest how will I integrate ur code in my procedure. – som_1522 Sep 11 '19 at 14:40
I forgot to change the data type in the xml query - sorry. – Doug Coats Sep 11 '19 at 14:59

score 0 · Answer 2 · answered Sep 12 '19 at 01:59

0

Consider the following:

declare @role varchar(20) = 'BACKOFFICE', 
    @PRIVILEGEID varchar(100) = '2,3,4,5,6';

select @role, * 
from string_split(@PRIVILEGEID, ',');

Using that select, you should be able to insert it into whatever table you'd like.

answered Sep 12 '19 at 01:59

Ben Thul

31,080
4
45
68

string_split is a function? – som_1522 Sep 12 '19 at 08:08
Yes. It was added in SQL 2016: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017 – Ben Thul Sep 12 '19 at 11:49

How to Pass Parameter as a List In a Stored procedure in a dynamic Insert Query

2 Answers2