IF EXISTS (SELECT 1 FROM RoleUser WHERE User_Id = 12346 AND Role_Code = 'CRC')
	UPDATE	RoleUser
	SET	Access		= 1,
		Worklist	= 0,
		Supervisor	= 0
	WHERE	Role_Code	= 'CRC'
	AND	User_Id		= 1246
ELSE
	INSERT
	INTO	RoleUser 
		(
		Role_Code,
		User_id,
		Access,
		Worklist,
		Supervisor
		)
	VALUES	(
		'CRC',
		12346,
		1,
		0,
		0
		)

List of Roles Below
('CHK',
'CJA',
'CJM',
'CON',
'CRC',
'DRW',
'INF',
'NAP',
'NBO',
'NCA',
'NCH',
'NCO',
'NDC',
'NDO',
'NER',
'NFP',
'NFR',
'NFU',
'NLE',
'NNP',
'NPO',
'NRD',
'NRE',
'NSA',
'NSC',
'NSP',
'NSS',
'NSX',
'PRN',
'RCV',
'RRE',
'SMF',
'SMR',
'SMX',
'APP')

• Edited by aidan1972 Friday, July 17, 2015 9:49 AM

the question is ?

Hi aidan1972,

The code in your description is to insert or update table RoleUser for a given User_Id and Role_Code, as per the question title, are your requiring a stored procedure which can handle mulptile User_Id and Role_Code? If my understanding is correct, you can reference the below sample.

USE TestDB;
GO

CREATE TYPE UserList AS TABLE 
( [User_id] INT
, Role_Code VARCHAR(99) );
GO

CREATE TABLE RoleUser 
(Role_Code VARCHAR(99),
 User_id INT,
 Access INT,
 Worklist INT,
 Supervisor INT
		);
GO


CREATE PROC yourProc (@userList UserList READONLY)
AS
BEGIN
	MERGE RoleUser AS Tar
		USING @userList AS Src
	ON Tar.[User_id]=Src.[User_id] AND Tar.Role_Code=Src.Role_Code
	WHEN MATCHED THEN
		UPDATE SET Tar.Access=1, Tar.Worklist=0,Tar.Supervisor=0
	WHEN NOT MATCHED THEN
		INSERT (Role_Code,[User_Id],Access,Worklist,Supervisor)
		VALUES (Src.Role_Code,Src.[User_id],1,0,0)
    ;
END
GO

DECLARE @userList UserList
INSERT INTO @userList VALUES(12346,'CRC'),(12456,'CHK')
 
EXEC yourProc @userList

SELECT * FROM RoleUser

If you have any question, feel free to let me know.