SQL Server loop through dynamic query

Question

I have a procedure where I pass temporary table name as parameter. For each dataset inside I need to get rowcount. How to achieve this?

I need something like:

CREATE PROCEDURE sp_processing 
  @temp_table_name varchar(50)
AS
  DECLARE @log varchar(max)

/* get list of keys inside temp_table_name */
/* just a pseudo-code example */
SET @l_cursor = CURSOR FOR 
SELECT Key1, Key2, Key3, count(*) 
  FROM @temp_table_name -- table name passed as text 
 GROUP by Key1, Key2, Key3;

WHILE "there are results"
BEGIN
  @log += @Key1 +', '+ @Key2 +', '+ @Key3 +', '+ @count + CHAR(13)+CHAR(10);
END

UPDATE log_table SET log_column = @log WHERE....;

END /* procedure */

Is there a way to loop this?

I know I have option to fetch results to a table type and THEN loop, but that requires to use a table type, so wondering if this is achievable without a table variable.

EDIT: I need just need to print count for each set of keys.

I don't quite understand what you are trying to do. If you recieve the temporary table name as parameter, then you will have to access it through dynamic SQL. Why do you need a cursor for it? Why do you need a loop? — EzLo
– EzLo, Commented May 10, 2018 at 9:00
Actually you're right, I don't need a loop because I just need to print the counts in a varchar variable, but I guess I need to loop through the records :-( — Jakub P
– Jakub P, Commented May 10, 2018 at 9:46
Include your complete use case in the question so we can avoid the Q&A ping pong. — EzLo
– EzLo, Commented May 10, 2018 at 9:49
I found this solution quite neat: stackoverflow.com/questions/8005846/… — Jakub P
– Jakub P, Commented May 10, 2018 at 10:15

Jakub P · Accepted Answer · 2018-05-10 10:44:11Z

2

This worked for me:

DECLARE @l_sql nvarchar(max)
DECLARE @temp_table_name varchar(50) = 'SOME_TABLE'
DECLARE @combinedString varchar(max)
SET @l_sql = 'SELECT @combinedString = COALESCE(@combinedString, '''') + convert(varchar,[Key1]) +'', ''+ convert(varchar,[Key3]) +'': ''+ convert(varchar,COUNT(*)) + ''| '' + CHAR(13)+CHAR(10) '
           + '  FROM ' + @temp_table_name  
           + ' GROUP BY [Key1], [Key3]'
           + ' ORDER BY [Key1], [Key3]';

EXECUTE sp_executesql @l_sql, N'@combinedString varchar(max) OUTPUT', @combinedString = @combinedString OUTPUT ;

SELECT @combinedString

Result:

1, 1: 4| 
1, 2: 2| 
1, 3: 1| 
2, 5: 1|

edited May 10, 2018 at 10:44

answered May 10, 2018 at 10:32

Jakub P

5425 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

EzLo · Accepted Answer · 2018-05-10 10:10:46Z

1

You should always try to avoid looping and cursor. This is a set based solution for your case. Please review (specially the update filter) and see if it suits your needs.

CREATE PROCEDURE sp_processing 
  @temp_table_name varchar(50)
AS
BEGIN

    DECLARE @DynamicSQL VARCHAR(MAX) = '

        ;WITH LogRecords AS
        (
            SELECT
                LogRecord = 
                    ISNULL(T.Key1, '''') + '','' + 
                    ISNULL(T.Key2, '''') + '','' +  
                    ISNULL(T.Key2, '''') + '','' + 
                    CONVERT(VARCHAR(20), COUNT(1))
            FROM
                QUOTENAME(''' + @temp_table_name + ''') AS T
            GROUP BY
                T.Key1,
                T.Key2,
                T.Key3
        )
        UPDATE L SET
            log_column = STUFF(
                (
                    SELECT
                        R.LogRecord + CHAR(13) + CHAR(10)
                    FROM
                        LogRecords AS R
                    FOR XML
                        PATH('')
                ),
                1, 1, '')
        FROM
            log_table AS L
        WHERE
            L.IdFilter = 999999999'

    PRINT @DynamicSQL

    -- EXEC (@DynamicSQL)

END

answered May 10, 2018 at 10:10

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

2 Comments

Jakub P Over a year ago

Thanks for effort. What do You think of this answer, which I find very neat: stackoverflow.com/questions/8005846/…

EzLo Over a year ago

The first answer has 2 solutions. The first one only works when filtering a particular set (won't work with group by), the 2nd one is similar to the one I wrote here. However, you can't escape using Dynamic SQL as long as you recieve the table name by parameter.

Collectives™ on Stack Overflow

SQL Server loop through dynamic query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related