table valued parameter comparison - update/delete

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 2008 Forums

Transact-SQL (2008)

table valued parameter comparison - update/delete

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-11-13 : 15:39:54

I have a simple table type:

CREATE TYPE [dbo].[int_list_tbltype] AS TABLE(
	[ID] INT  NULL
)
GO

It's populated and goes into the stored procedure just fine.
And the sql below works great.


@ID INT,
@UpdatedByID INT,
@RoleIDList dbo.int_list_tbltype READONLY

DELETE FROM UserRoles WHERE UserID = @ID

INSERT INTO UserRoles (UserID, RoleID, UpdatedBy, UpdatedDate) 
SELECT @ID, ID, @UpdatedByID , GETDATE() FROM @RoleIDList

Even though roles my not be updated often, I still don't want to delete all roles for a user then add them again, taking or adding some, or even deleting all of them then adding them all again, even if there were no changes especially since this isn't all the sp does.

Is there a way to compare what is in the role table vs. what is in the table param?

Then insert and delete only if needed.

Thank for any input.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-13 : 16:00:48

I haven't tested it but a MERGE statement should work even if your <table source> is a table type type.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources