SQL dynamic columns and Update multiple columns

Question

I have a table UserPermission which has a number of columns of TINYINT type. e.g Read, Write, Update, Delete, Access etc.

I get three parameters in the stored procedure: @UserId, @ColNames, @ColValues where @ColNames and @ColValues are comma separated values.

How can I insert or update the table row (if already exists) with the passed column names and corresponding values.

I try to write the dynamic query which runs fine for INSERT but I was unable to write the UPDATE query dynamically with each column and its value to be concatenate.

Any response would be appreciated

Thanks in advance.

Sample data and expected result will be more helpful. Also add the query you tried so far — Pரதீப், Nov 16 '16 at 17:09
the problem for the update statement is you will need to split the strings and maintain ordinal position to then correlate the 2 tables via ordinal position and the re concatenate. Or I suppose loop and update 1 column at a time but the latter isn't very good. Generally speaking this method of updating/inserting isn't recommended. — Matt, Nov 16 '16 at 17:16
Yes, MERGE. Also, if the INSERT worked, the UPDATE should work as well. Try to print your statement just before its execution and post it here. — FDavidov, Nov 16 '16 at 17:17
@Matt, I disagree. If you have the column name and the value and the filter (for the where), you can definitely build a working UPDATE command (I mean one that will work as needed). — FDavidov, Nov 16 '16 at 17:18
@FDavidov I am all for learning another method post what you are thinking — Matt, Nov 16 '16 at 17:42
@Matt, re-reading your comment (the one on which I wrote _I disagree_), you are in fact describing the correct method. My disagreement is mainly on the judgment about being **not recommended**, the reason being that, given the nature of the input (in this case) you have no other alternative but work this way (both for INSERT, UPDATE and MERGE). Beyond that, I guess we are fully synchronized. Cheers. — FDavidov, Nov 17 '16 at 05:11

score 0 · Answer 1 · answered Nov 16 '16 at 17:34

This is a somewhat dirty way to do what you require. However, if you create the following Stored Procedure:

CREATE FUNCTION [dbo].[stringSplit]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

You can then use that Procedure to join the data together:

DECLARE @TotalCols INT
DECLARE @TotalVals INT

SET @TotalCols = (
        SELECT COUNT(ID) AS Total
        FROM dbo.stringSplit('department, teamlead', ',')
        );
 SET @TotalVals = (
         SELECT COUNT(ID) AS Total
         FROM dbo.stringSplit('IT, Bob', ',')
         );

 IF @TotalCols = @TotalVals
 BEGIN
     IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
         DROP TABLE #temptable

     CREATE TABLE #temptable (
        ColName VARCHAR(MAX) NULL
        ,ColValue VARCHAR(MAX) NULL
        )

     INSERT INTO #temptable
     SELECT a.DATA
         ,b.DATA
    FROM dbo.stringSplit('department, teamlead', ',') AS a
    INNER JOIN dbo.stringSplit('IT, Bob', ',') AS b ON a.Id = b.Id

    SELECT *
    FROM #temptable;
END

It's not very efficient, but it will bring you the desired results.

You can then use the temp table to update, insert and delete as required.

Hi cyclington, Thanks for the response. however its returning the multiple rows instead of single row with each value as column name. how can i transform the rows into columns — Abdullah, Nov 17 '16 at 09:32

M.Ali · Answer 2 · 2016-11-16T17:49:22.080

Instead of having a comma delimited list I would create a separate parameter for each Column and make its default value to NULL and in the code update nothing if its null or insert 0. Something like this....

CREATE PROCEDURE usp_UserPermissions
 @UserID INT 
,@Update INT = NULL  --<-- Make default values NULL
,@Delete INT = NULL
,@Read   INT = NULL
,@Write  INT = NULL
,@Access INT = NULL
AS
BEGIN
  SET NOCOUNT ON;

Declare @t TABLE (UserID INT, [Update] INT,[Read] INT
                 ,[Write] INT,[Delete] INT,[Access] INT)

INSERT INTO @t (Userid, [Update],[Read],[Write],[Delete],[Access])
VALUES (@UserID , @Update , @Read, @Write , @Delete, @Access)   


IF EXISTS (SELECT 1 FROM UserPermission WHERE UserID = @UserID)
  BEGIN 
       UPDATE up        -- Only update if a value was provided else update to itself
        SET up.[Read]   = ISNULL(t.[Read]   , up.[Read])
           ,up.[Write]  = ISNULL(t.[Write]  , up.[Write])
           ,up.[Update] = ISNULL(t.[Update] , up.[Update])
           ,up.[Delete] = ISNULL(t.[Delete] , up.[Delete])
           ,up.[Access] = ISNULL(t.[Access] , up.[Access])
       FROM UserPermission up 
       INNER JOIN @t t ON up.UserID = t.UserID
  END
ELSE 
  BEGIN
         -- if already no row exists for that User add a row 
         -- If no value was passed for a column add 0 as default
    INSERT INTO UserPermission (Userid, [Update],[Read],[Write],[Delete],[Access])
    SELECT Userid
         , ISNULL([Update], 0)
         , ISNULL([Read], 0)
         , ISNULL([Write], 0)
         , ISNULL([Delete], 0)
         , ISNULL([Access], 0)
    FROM @t
  END

END

The problem with this method is if a column allows a NULL value there is no way to distinguish if the NULL would be the intended value or simply means missing a value is not intended to be changed. I am trying to find an answer that I and a few others wrote on this will post when I find. — Matt, Nov 16 '16 at 17:50
@Matt Since this is a permissions table I would imagine a user either have permission or NOT, we dont want to put an unknown permission in the permissions table after all it is security of the application, Anyway what you are asking for can simply be handled in the code by removing the ISNULL function in the very last insert, the rest will still remain the same. Thanks — M.Ali, Nov 16 '16 at 17:54
If nulls are not allowed in columns then no problem the method works well, if not removing both ISNULL and the DEFAULT values for the parameters would be necessary because thew would not longer be optional. I found that question which goes into depth on this type of structure: http://stackoverflow.com/questions/38952832/sql-updating-optional-parameters-php — Matt, Nov 16 '16 at 18:06
I cannot use the individual columns as its a huge list. Also the passed columns would not always be the all columns, it would be subset of the actual cols only. — Abdullah, Nov 17 '16 at 09:33

SQL dynamic columns and Update multiple columns

2 Answers2