See faq #8130 How to return a value from dynamicaly executed T-SQL code.

>Hi, I have the following stored procedure - the purpose of which is to determine if a branch record exists in any of two tables. The sp output param '@TotalCount' returns the total number of records that have the branchid in both tables.
>
>The following SP has the branchid hardcoded and it works:
>
>

>
>CREATE PROCEDURE  up_CountRecordsforBranch
>                  @BranchID             varchar(3),
>    	          @TotalCount           int output
>AS
>
>SET NOCOUNT ON
>
>DECLARE  @CurrentCount  int
>
>Set  @CurrentCount = 0
>
>Set @CurrentCount = (select count(*) from GLDetails where CUBranchVC = '001')
>set @TotalCount = @TotalCount + @CurrentCount
>
>set @CurrentCount = (select count(*) from TransactionDetails where CUBranchVC = '001')
>set @TotalCount = @TotalCount + @CurrentCount
>	
>
>GO
>
>

>
>
>In this SP - I'm trying to use dynamic SQL by passing in the branchid. When I try to run this, I get an error message "Must declare the variable '@CurrentCount'."
>
>

>
>CREATE PROCEDURE  up_CountRecordsforBranch
>                  @BranchID             varchar(3),
>    	          @TotalCount           int output
>AS
>
>SET NOCOUNT ON
>
>DECLARE  @CurrentCount  int
>
>Set  @CurrentCount = 0
>
>EXEC('set @CurrentCount = (select count(*) from GLDetails where CUBranchVC = ' + @BranchID + ')')
>set @TotalCount = @TotalCount + @CurrentCount
>
>EXEC('set @CurrentCount = (select count(*) from TransactionDetails where CUBranchVC = ' + @BranchID + ')')
>set @TotalCount = @TotalCount + @CurrentCount
>	
>
>GO
>
>

>
>Any idea as to what I'm doing wrong here?