title: How to Pass Parameter as a List In a Stored procedure in a dynamic Insert Query
body: <p>I have a scenario that @PRIVILEGEID will have multiple value and @ROLEID will be same every time for those privilege Id.</p>
  
  <p>So I have to insert the data into table.
  Below is the Code For Procedure:-</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>Exec ADD_ROLE 'BACKOFFICE',@PRIVILEGEID  @PRIVILEGEID=[2, 3, 4, 5, 6]</p>
  
  <p>-- (How to pass multiple value for PRIVILEGEID )</p>
  
  <p>I have tried While loop also but not sure how to implement it.</p>
  
  <p>Please help.</p>