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