Vishnu
You are right, Exec is in a different scope. In fact it is a different 
connection.

You can do it.
Try using sp_executesql. This is SQL Servers preferred method of doing 
dymanic queries as it can re-use 'plans' better then EXEC (which always 
creates a new one). This is especially true when the where clause changes.
Look this up in Books on Line(BOL)

But here is some code so you can see it happening.


/***********************************************************
use northwind
declare @RECCnt int
Declare @NParmDefin nvarchar(200)
Declare @NSQLstr nvarchar(1000)
Declare @TableName sysname
set @NParmDefin=N'@N... int OUTPUT'
set @TableName = 'customers'
set @NSQLstr =N'Select @NRecCnt=count(*) from '+@TableName

Execute sp_executesql @NSQLStr,@NParmDefin,@NRecCnt=@RecCnt OUTPUT

Select @RecCnt
************************************************************/
You can use as many (up to 1024 i think big any way) OUTPUT variables  as 
you want

Regards

Greg Frazer

> > Hi there,
>>  
>>  I am building a dynamic sproc that can handle inserts for multiple 
t> ables 
>>  that all share the 'Description' column. The table name gets passed in 
a> s 
>>  a parameter, a SQL statement is built and then executed using EXEC
>>  
>>  I am trying to find out whether an item in this table already exists. 
I 
>>  can build a dynamic 'SELECT Count(*) as Count FROM ' + @sTableName 
>>  statement and execute that. But how do I return the value of count? 
EXEC 
>>  does not return a value, and if I assign the count to a variable from 
>>  within the executed SQL code, I get a message that the variable is not 
>>  declared (apparently, EXEC has a different scope).
>>  
>>  Another solution I came up with is using a dynamically created cursor, 
a> nd 
>>  see if I can fetch from that. This works, but looks pretty awful to 
me. 
I>  
>>  also don't want to use a temp table, as this seems way too much 
overhead 
>>  for a simple solution.
>>  
>>  I am posting the CURSOR solution here, to make clear what I am trying 
to 
>>  accomplish.
>>  Does anybody have a faster / cheaper solution to this?
>>  
>>  Thanks in advance.
>>  
>>  ---- CODE ----
>>  CREATE PROCEDURE spInsertItem
>>     @sDescription varchar(50),
>>     @sTableName varchar(30)
>>  AS
>>     DECLARE @sSQLStatement varchar(200)
>>     DECLARE @iID int
>>     SELECT @sSQLStatement = 'DECLARE myCursor CURSOR FOR SELECT ID 
FROM ' 
+>  
>>  @sTableName + ' WHERE Description =''' + @sDescription + ''''
>>     EXEC (@sSQLStatement)
>>     OPEN myCursor
>>     FETCH NEXT FROM myCursor INTO @iID
>>     IF (@@FETCH_STATUS = 0) -- There is an item
>>     BEGIN
>>        PRINT 'Item exists'
>>     END
>>     ELSE
>>     BEGIN
>>        PRINT 'Item does not exist'
>>        -- Item doesn't exist, so build dynamic INSERT statement here
>>     END			
>>     CLOSE myCursor
>>     DEALLOCATE myCursor