CODEWITHSUNDEEP
sql
SouthSideRob
SouthSideRob

Reputation: 21

Return Row Count Using Dynamic SQL

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)

Upvotes: 2

Views: 6566

Answers (2)

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 3

radar
radar

Reputation: 13425

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

Upvotes: -1

Related Questions