title: How do I write update query for setting Roles and permissions in stored procedure
body: <p>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</p>
  
  <p>Role</p>
  
  <pre><code>RoleId int,
  RoleName varchar(25),
  [Description] varchar(100),
  Deleted bit,
  CreatedOn datetime,
  CreatedBy int,
  LastUpdatedOn datetime,
  LastUpdatedBy int
  </code></pre>
  
  <p>Permission</p>
  
  <pre><code>PermissionId int,
  PermissionName varchar(25)
  </code></pre>
  
  <p>RoleDetail</p>
  
  <pre><code>RoleDetailId int,
  RoleId int,
  PermissionId int
  AddedOn datetime,
  AddedBy int,
  Deleted bit,
  DeletedOn datetime,
  DeletedBy int
  </code></pre>
  
  <p>Query</p>
  
  <pre><code>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
  </code></pre>