How do I write update query for setting Roles and permissions in stored procedure

Question

i have a stored procedure in which i have to update the values of Role table and then according to Permission table's Permissionid , i have to Update the third table Roledetail. but what i tried is not working and not updating the table.here is my stored procedure. please help.. i have following table

Role

RoleId int,
RoleName varchar(25),
[Description] varchar(100),
Deleted bit,
CreatedOn datetime,
CreatedBy int,
LastUpdatedOn datetime,
LastUpdatedBy int

Permission

PermissionId int,
PermissionName varchar(25)

RoleDetail

RoleDetailId int,
RoleId int,
PermissionId int
AddedOn datetime,
AddedBy int,
Deleted bit,
DeletedOn datetime,
DeletedBy int

Query

ALTER PROCEDURE usp_UpdateRole
@pRoleId int,
@pRoleName Varchar(25),
@pRoleDescription Varchar(100),
@pAttachedPermission varchar(50)
AS
BEGIN

    SET NOCOUNT ON;

   DECLARE @Permissions AS VARCHAR(50)
SET @Permissions = @pAttachedPermission
DECLARE @Query AS NVARCHAR (max)


UPDATE [Role]
SET

    [Role].[RoleName] = '@pRoleName',
    [Role].[Description] = '@pRoleDescription',
    [Role].[Deleted] = 0,
    [Role].[CreatedOn]= GetDate(),
    [Role].[CreatedBy] = 1,
     [Role].[LastUpdatedOn] = NULL,
    [Role].[LastUpdatedBy]=NULL 
    where [Role].RoleId = @pRoleId

DECLARE @RoleId AS INT
SET @RoleId = Scope_Identity()
SET @Query = 'SELECT '+
         CAST(@RoleId as Varchar(10))+' AS RoleId, 
        PermissionId AS PermissionId,
        GETDATE() AS AddedOn,
        1 As AddeBy,
        0 AS Deleted,
        NULL As DeletedOn,
        NULL AS DeletedBy 
    FROM
        [Permission]
    WHERE
        PermissionId in ('+@Permissions+')'
SET @Query = 'INSERT INTO [RoleDetail] '+ @Query
exec sp_ExecuteSQL @Query
END

please post sample data, what happens now when you run the proc, and what you want to happen. — Neville Kuyt, Dec 05 '14 at 11:32
want to update role table details and want to select permissions from permission table and storing that permissionids seprated with comma and want to update the roledetail table according to roleid with permissions, did u get me ? — , Dec 05 '14 at 11:40

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

You are trying to use scope_identity with update statement.

Your RoleId will be last ID inserted in your scope, not ID of your updated record. So basically you need to change the way how you get your role id.

But in general, i would rewrite your proc as follows

UPDATE [Role]
SET
....
    where [Role].RoleId = @pRoleId 

INSERT INTO [RoleDetail] (RoleId, PermissionId, AddedOn, AddedBy, Deleted )
SELECT  @pRoleId, PermissionId, getdate(), 1, 0
FROM  [Permission]
WHERE
PermissionId in (select id from fn_parseIntList(@Permissions))

Where parseIntList is function which converts string list into table data of int. There are a lot of examples how to implement this function e.g. Converting String List into Int List in SQL

UPD: After getting table structure - you need to insert only 2 columns, assuming RoleDetailId is identity

INSERT INTO [RoleDetail] (RoleId, PermissionId)
    SELECT  @pRoleId, PermissionId
    FROM  [Permission]
    WHERE
    PermissionId in (select id from fn_parseIntList(@Permissions))

edited May 23 '17 at 12:13

Community

1
1

answered Dec 05 '14 at 11:51

fly_ua

1,034
8
12

i have updated my table , please refer this and suggest where i am doing wrong. – Dec 05 '14 at 12:12
can you send me new sources ? – fly_ua Dec 05 '14 at 12:15
sorry. i didnt get you . i have updated my table description can you please update you answer according to updated table – Dec 05 '14 at 12:20
RoleDetail contains only 3 columns? But you are inserting 5 in you stored procedure - i will update and leave only present columns – fly_ua Dec 05 '14 at 12:26
i am getting error on this line of code select id from fn_parseIntList(@Permissions – Dec 05 '14 at 12:43
Read answer please - Where parseIntList is function which converts string list into table data of int. There are a lot of examples how to implement this function e.g. Converting String List into Int List in SQL – fly_ua Dec 05 '14 at 12:46
Thank you for you time i simply avoid using scope_identity and it works . thanks for your suggestion . – Dec 05 '14 at 12:58

score 1 · Accepted Answer · answered Dec 05 '14 at 13:02

with some changes suggested by @ fly_ua its working as charm .

ALTER PROCEDURE usp_UpdateRole
@pRoleId int,
@pRoleName Varchar(25),
@pRoleDescription Varchar(100),
@pAttachedPermission varchar(50)
AS
BEGIN

    SET NOCOUNT ON;

   DECLARE @Permissions AS VARCHAR(50)
SET @Permissions = @pAttachedPermission
DECLARE @Query AS NVARCHAR (max)


UPDATE [Role]
SET

   [Role].[RoleName] = @pRoleName,
    [Role].[Description] = @pRoleDescription,
    [Role].[Deleted] = 0,
    [Role].[CreatedOn]= GetDate(),
    [Role].[CreatedBy] = 1,
     [Role].[LastUpdatedOn] = NULL,
    [Role].[LastUpdatedBy]=NULL 
    where [Role].RoleId = @pRoleId

SET @Query = 'SELECT '+
         CAST(@pRoleId as Varchar(10))+' AS RoleId, 
        PermissionId AS PermissionId,
        GETDATE() AS AddedOn,
        1 As AddeBy,
        0 AS Deleted,
        NULL As DeletedOn,
        NULL AS DeletedBy 
    FROM
        [Permission]
    WHERE
        PermissionId in ('+@Permissions+')'
SET @Query = 'INSERT INTO [RoleDetail] '+ @Query
exec sp_ExecuteSQL @Query
END

How do I write update query for setting Roles and permissions in stored procedure

2 Answers2