|I recently had a requirement to update multiple tables with the same value. We have a table that stores information about documents (Excel documents, Word documents, text documents, images, reports etc). Every document has an owner associated with it. This person has admin privileges over the document. After a discussion with one of our users, they wanted the ability to change the owner of a document. Doing this at the level of a single document is straight forward. However, the user wanted this for multiple documents. For example, if a user is due to leave the business, they wanted the ability to change the owner of all their documents to a new owner.
I therefore needed the ability to pass a list of document IDs into a stored procedure. The stored procedure would then change the owner for all the documents in the list to the specified owner. Passing in the comma-delimited list of document IDs wouldn't be difficult, as this is essentially a long string. The tricky part would be to iterate through the items in the list i.e. to fetch each document ID from the comma-delimited list so that the owner can be updated.
The first thing I needed to do was to create a function that could iterate through the list. I create a Table-Valued-Function (TVF) called Split to achieve this. If you don't already know, a TVF is a function that returns a table (as the name suggests). In our case, we will return a two column table containing a unique ID and an item from the list. So if there are 10 items in the list, then there will be 10 rows in the table returned by our TVF.
CREATE FUNCTION [dbo].[Split]
RETURNS @RtnValue table
Id int identity(1,1),
Insert Into @RtnValue (value)
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
ENDThe function has two paramters. The first is the comma-delimited list of document IDs
@List = '1, 2, 3, 4, 5'The second parameter is the delimiter. In this case we are passing a comma-delimited list hence the delimiter is a comma.
@SplitOn = ','The function loops through the list locating the next item by searching for the next occurrence of the delimiter. It keeps doing this until it cannot find any more occurrences of the delimiter. Each item it finds between the current and next delimiter is inserted into the table that will be returned by the TVF.
We next need to write a stored procedure that invokes our Split Table-Valued-Function.
CREATE PROCEDURE [dbo].[Documents_UpdateOwner]
UploadedBy = @owner
ID IN (SELECT CONVERT(INT, Value) FROM Split(@documentids, ','))
ENDThere are two parameters to the stored procedure. The first one is the ID of the new owner for the documents. The second parameter is a comma-delimited list of document IDs for which we wish to change the owner. The items returned from the Split TVF are stored in string format. Therefore if we need to update data in another format we need to do a conversion. In our case, we are updating an INT and therefore need to convert the item from an NVARCHAR to an INT. Obviously we wouldn't need to do any conversion if we were comparing against string data.
I have since used this Table-Valued-Function in other stored procedures where I need to iterate through a list of items. It's a very efficient way of updating multiple tables. Instead of having to make multiple calls to a stored procedure to update each document owner, I can instead make one call to a stored procedure and update all of them at once. This is a neat way to allow for those scenarios where you need to update data from a list of items.
"There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult." - C.A.R. Hoare