Return Row Count Using Dynamic SQL

Question

I'm trying to run the following Dynamic SQL statement:

@Tbl, @Fld, and @LookupValue have all been set according to Table to search, Field (Or Column) to search and column value to compare.

DECLARE @Sql AS VARCHAR(500)
SET @Sql = 'SELECT COUNT(*) 
            FROM ' + @Tbl +  
            ' WITH (NOLOCK) 
            WHERE ' + @Fld + ' = ''' + @LookupValue + ''''

EXEC(@Sql)

I want to store the result into a variable so I can check to see if there are any returned rows. This statement is in the middle of a WHILE construct that is checking several tables and fields.

If records are found, then I want to display:

SET @Sql = 'SELECT ' + @Fld +
           ' FROM ' + @Tbl + 
           ' WITH (NOLOCK) 
           WHERE ' + @Fld + ' = ''' + @LookupValue + ''''

EXEC(@Sql)

@RADAR, have to be SQL Server since there is no NOLOCK table hint in MySQL. — Rahul
– Rahul, Commented Nov 14, 2014 at 18:51

GilM · Accepted Answer · 2022-12-17 07:12:54Z

3

Yes, you can store it in a typed variable and use sp_executesql like

DECLARE @Sql AS NVARCHAR(500);
DECLARE @cnt INT;

SET @Sql = 'SELECT @cnt = COUNT(*) 
            FROM ' + @Tbl +  
            ' WITH (NOLOCK) 
            WHERE ' + @Fld + ' = ''' + @LookupValue + '''';

EXEC sp_executesql @Sql, N'@cnt INT OUTPUT', @cnt OUTPUT;

SELECT @cnt;

edited Dec 17, 2022 at 7:12

GilM

3,78120 silver badges18 bronze badges

answered Nov 14, 2014 at 18:57

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Comments

radar · Accepted Answer · 2014-11-14 18:54:40Z

-1

you can create a temporary table and store the count value.

if object_id('tempdb.#mycount')  is null 
create table #mycount ( countVal int);

DECLARE @Sql AS VARCHAR(500)
SET @Sql = 'INSERT INTO #mycount 
            SELECT COUNT(*) 
            FROM ' + @Tbl +  
            ' WITH (NOLOCK) 
            WHERE ' + @Fld + ' = ''' + @LookupValue + ''''

EXEC(@Sql)

select countVal from #mycount


-- once the temp table usage is done, you can delete it
drop table #mycount

answered Nov 14, 2014 at 18:54

radar

13.4k2 gold badges27 silver badges33 bronze badges

Collectives™ on Stack Overflow

Return Row Count Using Dynamic SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related