How to execute command from store procedure?

[nguyentaiuyenchi]

Hi all,

I have index table (tblIndexTable) that contain name of table (at 'tablename' field)- that mean number of table is not specify. I make a store procedure as follows:

-------------------------------------------------------
declare @table_name varchar(50)
declare @applicant int
declare @strSql Nvarchar(500)

declare curTable cursor for
SELECT TableName FROM tblIndexTable
open curTable
FETCH NEXT FROM curTable into @table_name

While (@@FETCH_STATUS=0)
begin

set @strSql = 'SELECT @applicant=COUNT(*) FROM ' + @table_name
exec (@strSql)
print @applicant
FETCH NEXT FROM curTable into @table_name
end
CLOSE curTable
DEALLOCATE curTable
------------------------------------------

Execute the above store I got error with message as
'Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@applicant'.

Please show me how to count number of records for Tables.

Thank in advance,

Uyen Chi Uyen Chi
Software developer

 

[sunila7]

hi,

have a look at this thread. thread183-504942

Sunil

 

[ionutcalin]

Try this one:

declare @table_name varchar(50)
declare @applicant int
declare @strSql Nvarchar(500)

declare curTable cursor for
SELECT TableName FROM tblIndexTable
open curTable
FETCH NEXT FROM curTable into @table_name

While (@@FETCH_STATUS=0)
begin

set @strSql = 'set @ret=(select COUNT(*) FROM ' + @table_name + ')'
EXEC sp_executesql @strSql,
N'@ret int OUTPUT',
@ret=@applicant OUTPUT
print @applicant
FETCH NEXT FROM curTable into @table_name
end
CLOSE curTable
DEALLOCATE curTable


IONUT