Problems with returning a value

[Rocket452]

Using T-SQL with SQL Server 2005

heres the code for my stored procedure:

declare @Count varchar(50) --ask
declare @sql varchar(200)
declare @db_nm varchar(50)
declare @tbl_nm varchar(50)
declare @file_tbl_yr varchar(50)

declare Status_Cursor cursor
for
select db_nm, tbl_nm, file_tbl_yr
from cims_load_stats
where db_nm is not null
open Status_Cursor
fetch next from Status_Cursor into @db_nm,@tbl_nm,@file_tbl_yr
while (@@fetch_status <> -1)
begin
set @sql = ('select count(*) from ' + @db_nm + '..'+ @tbl_nm +
' where reins_yr_id = ' + @file_tbl_yr)

set @Count = exec @sql --PROBLEM IS HERE

set @sql = 'update CIMSTEAM.dbo.CIMS_LOAD_STATS
set REC_IMPORTED = ' + @Count +
' where db_nm = ' + '''' + @db_nm + '''' +
' and tbl_nm = ' + '''' + @tbl_nm + '''' +
' and File_tbl_yr = ' + @file_tbl_yr

(there is more code after this but I didnt think it was relevant)

The problem I'm having is with the:
set @Count = exec @sql
I assumed it was a datatype problem considering the @sql returns an int and @Count is a varchar, but I tried changing @Count to an int but it didnt work.

Thanks in advance

[Shuja Ali]

You should not be using Dynamic SQL. It affects performance. And to get a simple count, you could use something like this Declare @Count Int
Select @Count = Count(*) From TABELNAME
Also make sure you use Code tags when you post code, take a look at our FAQS.

[Rocket452]

I have to put in the database as well as the table name in this format:
databaseName..tableName

so can i do this?

set @Count = select count(*) from @db_nm + '..'+ @tbl_nm where reins_yr_id = @file_tbl_yr

[Shuja Ali]

You should try re-looking at your logic. There is no advantage of using a Stored procedure when you write dynamic SQl inside them. The query doesn't get compiled and you don't take advantage of writing the SP. I don't have enough time, otherwise I would have shown you how to do it. Take a look at this
http://www.sommarskog.se/dynamic_sql.html