execute for count inside cursor, sql server

Question

I have a requirement where i have to find out the number of records for each table names which are already listed in a master table.

the master table looks like: RecordId, TableName, RowCount initially all the rows for RowCOunt set to 0.

what i did was, created a cursor

declare @tName nvarchar(max)
declare @query nvarchar(max)
declare @count int = 0
declare curCount cursor for select TableName from MasterTable
open curCount
fetch next from curCount into @tName
while @@fetch_status=0
begin
    set @tName = @tName
    set @query = N'select count(ID) from ['+@tName+']';
    set @count = execute @query
    update @tempTbl set RecordCount = @count where TableName  = @tName
    print @query
    fetch next from curCount into @tName
end
close curCount
deallocate curCount

it gives me error every time at this point set @count = execute @query, error is: Incorrect syntax near the keyword 'exec'.

i have tried sp_executesql @query as well... it also gives me error, error is Incorrect syntax near '@query'.

Please help me to fix this.

See http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable for returning a result from sp_executesql — artm, Aug 03 '16 at 05:06

score 0 · Answer 1 · answered Aug 03 '16 at 05:22

You can use sp_executesql with an output parameter to get the value . Please try this :

declare @tName nvarchar(max)
declare @query nvarchar(max)
declare @count int = 0
declare curCount cursor for select TableName from MasterTable
open curCount
fetch next from curCount into @tName
while @@fetch_status=0
begin
    set @tName = @tName
    set @query = N'select count(ID) from ['+@tName+']';
    --set @count = execute @query

    EXEC sp_executesql @query, 
                   N'@count int OUTPUT', 
                   @count = @count OUTPUT


    update @tempTbl set RecordCount = @count where TableName  = @tName
    print @query
    fetch next from curCount into @tName
end
close curCount
deallocate curCount

score 0 · Answer 2 · answered Sep 19 '19 at 10:04

0

worked for me only when I added the variable in the select statement as well. Replace this: set @query = N'select count(ID) from ['+@tName+']'; with this: set @query = N'select @count=count(ID) from ['+@tName+']';

answered Sep 19 '19 at 10:04

spambo01

31
8

execute for count inside cursor, sql server

2 Answers2