There is no built in function in SQL to split the comma separaed values. You have to create user defined function which split the comma separated data..
Please refer following link containing the details of User Defined function used to split the data..
Split function in SQL[
^]
Once you created the user defined function to split data in SQL, you can insert values into your table as following
CREATE PROCEDURE procTest(@roleID INT, @privileges VARCHAR(50))
AS
BEGIN
INSERT INTO tblRoles(RoleID, Roles)
SELECT @roleID, value FROM dbo.[NAME-OF-USER-DEFINED-SPLIT-FUNCTION](@privileges,',')
END