Updating child table;with only needed items

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updating child table;with only needed items

Author

Topic

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2011-03-22 : 03:15:29

I have three tables:
• Role
• Permission
• RolePermission (link table with just role id and permissionid)
I have a stored procedure that updates role table
If there are associated permissions to the role,I also have to update
RolePermission table too.
If I am passing permissionIds associated with the role to the update stored procedure, as a comma separated varchar (4000) I can use the function dbo.Split to split them good.
A bad query I can write is
UPDATE [ROLE] SET
ProductId=@ProductId,
[Name]=@Name
WHERE RoleId=@RoleId

DELETE FROM RolePermission WHERE RoleId=@RoleId
INSERT INTO RolePermission
SELECT @RoleId, P.Items
FROM dbo.Split(@PermisionIds, ',') P
But I would like to add only those ids new in @PermisionIds and not in the table
And delete all PermisionIds from table which are not in @PermisionIds.
How can i do this without using IN and NOT IN

matty
Posting Yak Master

161 Posts

Posted - 2011-03-22 : 04:42:24

[code]
--get new Id's in @PermisionIds
SELECT p.Items
FROM dbo.Split(@PermisionIds,',') p
LEFT JOIN RolePermissions rp ON p.Items = rp.PermissionId AND rp.RoleId = @RoleId
WHERE rp.PermissionId IS NULL

--Get Id's from table which are not in @PermisionIds
SELECT rp.PermissionId
FROM RolePermissions rp
LEFT JOIN dbo.Split(@PermisionIds,',') p ON p.Id = rp.PermissionId
WHERE rp.RoleId = @RoleId
AND p.Items IS NULL
[/code]

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2011-03-23 : 10:49:54

still i need to use IN and NOT in ,right?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources