Problems with returning a value

Using T-SQL with SQL Server 2005

heres the code for my stored procedure:

Code:

---

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

You should not be using Dynamic SQL. It affects performance. And to get a simple count, you could use something like this

Code:

---

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.

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

so can i do this?

Code:

---

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

---

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