UDF Field Reference In SQL Server UPDATE statement

Question

I have an UPDATE statement in SQL Server that runs several UDFs encapsulating necessary business logic. So a user can opt in or opt out of communications with our company and we have a bunch of permission codes corresponding to the various communications we send people. We not only have a permission field but we have a Do Not Promote field as well. If someone has opted out of a permission then we want to move it and its associated permissions to the DNP field.

So Lets say a user has the following field values

PermissionCodes                DoNotPromoteCode
A|B|C|D|                       Z|

Now the user opts out of A but when he opts out of A then our company wants to opt him out of codes C and G as well and add those values to the DoNotPromote field, but only add them to the DNP if they were in the Permissions field. i.e.

PermissionCodes                DoNotPromoteCode
B|D|                           Z|A|C|

So I have this query:

UPDATE Permissions
SET PermissionCodes = dbo.RemovePermissions('A'),
    DoNotPromoteCodes = dbo.AddDoNotPromote('A', PermissionCodes)
WHERE Email = 'john.doe@yahoo.com'

The UDF "dbo.RemovePermissions" will remove the Permission you pass in and all other Permissions that are related to it. The UDF "dbo.AddDoNotPromote" adds the permission to the DoNotPromote field and then searches PermissionCodes for relatives to that codes and adds them to the DoNotPromote Field.

So, my question is, since I am updating the PermissionCodes field at the same time I am referencing it in the dbo.AddDoNotPromte UDF, when during the update statement is the PermissionCodes field being read within the dbo.AddDoNotPromote UDF? So far I am getting the results that I was hoping for, but is it always guaranteed that dbo.AddDoNotPromote will read the values in the PermissionCodes field before dbo.RemovePermissions removes those values?

score 0 · Accepted Answer · edited May 23 '17 at 11:56

To actually answer your question, it is always the values before the update that are used, so with this table:

A   |   B
----+-----
1   |   2
3   |   4

Running:

UPDATE  T
SET     A = B,
        B = 1;

Will give:

A   |   B
----+-----
2   |   1
4   |   3

It does not run in the order of the statements within the update.

However, if it is not too late you should seriously consider redesigning your tables, storing delimited values in a text column is a terrible idea.

You would be much better off storing your data in a normalised form, so you would have a table structure like:

PermissionCode

PermissionCode
-------
A               
B               
C
D
Z

UserPermission

UserID  |   PermissionCode
--------+--------------------
1       |   A
1       |   B
1       |   C
1       |   D

You can then use another other table to manage linked Permissions:

ParentCode  |   ChildCode
------------+---------------
    A       |       C
    A       |       G

You can then get all permissions by a user using this table, e.g. by creating a view:

CREATE VIEW dbo.AllUserPermission
AS
SELECT  p.UserID, p.PermissionCode
FROM    UserPermission p
UNION 
SELECT  p.UserID, lp.ChildCode
FROM    UserPermission p
        INNER JOIN LinkedPermission lp
            ON lp.ParentCode = p.PermissionCode;

Then you can get permissions that a user does not have using something like this:

SELECT  u.UserID, P.PermissionCode
FROM    UserTable u
        CROSS JOIN PermissionCode p
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    AllUserPermission up
            WHERE   up.UserID = u.UserID
            AND     up.PermissionCode = p.PermissionCode
        );

This way when you add new permissions you don't need to upate a column for all the users for DoNotPromoteCode, this is calculated on the fly by removing permissions the user has from a list of all permissions.

If you specifically need to store codes that people have expcitly opted out of in addition to those they are not receiving then you could add a column to the UserPermission table to store this, you can also store dates and times so you know when various actions were taken:

UserID  |   PermissionCode  |   AddedDateTime   |   DoNotPromoteDateTime    |   RemovedDateTime
--------+-------------------+-------------------+---------------------------+--------------------
1       |       A           | 2013-11-25 16:55  |           NULL            |       NULL
1       |       B           | 2013-11-25 16:55  |       2013-11-25 16:55    |       NULL
1       |       C           | 2013-11-25 16:55  |       2013-11-25 16:56    |   2013-11-25 16:57
1       |       D           | 2013-11-25 16:55  |           NULL            |   2013-11-25 16:57

By querying on whether certain columns are NULL or not you can determine various states.

This is a much more manageable way of dealing with a one to many relationship, pipe delimited strings will cause no end of problems, if you need to show the permission codes as a delimited string for any reason this can be achieved using SQL Servers XML extensions

UDF Field Reference In SQL Server UPDATE statement

1 Answers1