CODEWITHSUNDEEP
sql-servert-sqlfunctiondynamic-sqlsp-executesql
The Light
The Light

Reputation: 27021

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

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

Upvotes: 0

Views: 19586

Answers (2)

Steve Ford
Steve Ford

Reputation: 7763

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 |

Upvotes: 0

Ivan Golović
Ivan Golović

Reputation: 8832

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'.

Upvotes: 4

Related Questions