How to run a dynamic sql for each row of a table?

Question

I have a table which contains a column named WhereClause

DECLARE @UserGroups TABLE (WhereClause nvarchar(1000), totalCount int)

The table has been populated with some values for WhereClause but none for totalCount column.

An example of the WhereClause is "FirstName is null".

Basically for each row in the table, I'd need to calculate the COUNT(*) from another table (Users) and set the totalCount on the above table.

The below doesn't work:

SELECT UG.WhereClause, U.TotalCount
FROM @UserGroups as UG
OUTER APPLY (SELECT COUNT(*) as 'TotalCount' FROM [Users] WHERE UG.WhereClause)
) U

I tried creating a sql function and calling exec sp_executesql within the function but it's not supported.

CREATE FUNCTION [dbo].[fn_UserGroupCount]
(   
    @whereClause as NVARCHAR(1000)
)
RETURNS @returnValue TABLE (TotalCount INT)
AS
BEGIN
    DECLARE @stmt nvarchar(1500)

    SET @stmt = 'SELECT COUNT(*) FROM [dbo].[Users]
    WHERE '  + @whereClause

    Exec sp_executesql @stmt

RETURN
END

SELECT * FROM [dbo].[fn_UserGroupCount]('Subject is null')

How would that be possible to populate my @UserGroups table as mentioned?

Thanks

Indeed the idea looks nice but this approach will add to processing overload as the D-SQL will have to be executed **for each row**. Can you find another alternative for your requirement? — Rachcha, Jan 10 '14 at 11:50

Ivan Golović · Answer 1 · 2012-12-18T14:02:56.113

4

Try this, you'll have to use temporary table for dynamic SQL to be able to reach it (@UserGroups won't work so I replaced it with #UserGroups):

CREATE TABLE #UserGroups (WhereClause nvarchar(1000), totalCount int)
INSERT #UserGroups VALUES ('FirstName IS NULL', NULL)
INSERT #UserGroups VALUES ('FirstName IS NOT NULL', NULL)

DECLARE @sql VARCHAR(8000) = STUFF(
(
SELECT  ';UPDATE #UserGroups SET totalCount = (SELECT COUNT(*) FROM [Users] WHERE ' + WhereClause + ') WHERE WhereClause = ''' + WhereClause + ''''
FROM    #UserGroups
FOR XML PATH('')
), 1, 1, '')

PRINT @sql
EXEC(@sql)

SELECT  *
FROM    #UserGroups

DROP TABLE #UserGroups

However, problems might arise if WhereClause contains quotes, something like FirstName LIKE 'Joe'.

edited Dec 18 '12 at 14:02

answered Dec 18 '12 at 13:46

Ivan Golović

8,732
3
25
31

Thanks but I can't use it since it's not thread-safe. If 2 calls are made at the same time, it fails with temp tables. – The Light Dec 18 '12 at 15:02
You're welcome. Do you get some error? What does the error text say? Do you call this code from application? – Ivan Golović Dec 18 '12 at 17:43
1

I had a different issue but this answer gave me the clue I needed to fix it! thanks :) – David Aleu Oct 17 '14 at 11:45

Steve Ford · Answer 2 · 2012-12-21T13:27:25.157

I would use a cursor to build a string containing your update statements with the select count(*) from @whereclause built in. You can then execute this to perform your updates. See the SQLFiddle below:

UPDATED Now works where the where clause has single quotes in it and also apostrophes in where clauses

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table users
(
  userid int primary key identity,
  name varchar(50),
  subject varchar(50) null
);

INSERT INTO users (name, subject)
VALUES
    ('Fred', 'Maths'),
    ('Bill', null),
    ('Helen', 'English'),
    ('O''Keefe', 'Maths');

Query 1:

DECLARE @WhereClause nvarchar(1000)
DECLARE @stmt nvarchar(1500) = ''

CREATE TABLE #UserGroups (WhereClause nvarchar(1000), totalCount int)
INSERT #UserGroups VALUES ('Subject IS NULL', NULL)
INSERT #UserGroups VALUES ('Subject IS NOT NULL', NULL)
INSERT #UserGroups VALUES ('Subject =''Maths''', NULL)
INSERT #UserGroups VALUES ('name =''O''''Keefe''', NULL)

DECLARE cur CURSOR FOR
  SELECT WhereClause FROM #UserGroups

OPEN cur

FETCH cur  INTO @WhereClause

WHILE @@FETCH_STATUS = 0
BEGIN

  SELECT @stmt = @stmt + 
      'UPDATE #UserGroups ' +
      'SET totalCount = ' +
      '(SELECT COUNT(*) FROM users WHERE ' + @WhereClause + ') ' +
      'WHERE WhereClause = ''' + REPLACE(@WhereClause, '''', '''''') + ''';' 
  FETCH cur  INTO @WhereClause
END

CLOSE cur
DEALLOCATE cur

--select @stmt
exec(@stmt)

SELECT *
FROM #UserGroups

DROP TABLE #UserGroups

Results:

|         WHERECLAUSE | TOTALCOUNT |
------------------------------------
|     Subject IS NULL |          1 |
| Subject IS NOT NULL |          3 |
|    Subject ='Maths' |          2 |
|    name ='O''Keefe' |          1 |

That dynamic SQL isn't safe if `WhereClause` might include quotes. The `COUNT()` query is safe, but the comparison back to the `WhereClause` column isn't. — Damien_The_Unbeliever, Dec 21 '12 at 12:23
@Damien_The_Unbeliever updated so it handles quotes in the where clause. Might still have a problem with where clauses which have quotes in the comparison value e.g. name='O''Keefe' — Steve Ford, Dec 21 '12 at 13:06
@TheLight This should now work for you for all whereclauses with apostrophes and text field comparisons — Steve Ford, Dec 21 '12 at 13:28
I like to pretend that the `CURSOR` command does not exist unless I'm going to update the rows being iterated. I believe a simple while loop would do. — SQLMason, Nov 08 '13 at 14:10

How to run a dynamic sql for each row of a table?

2 Answers2