This is my sample sql query in a stored procedure which is working fine.
 But i want to pass the values of the concession, which is here 'TH-003853199' and table name dynamically..

```
SELECT @source = source,@stage = stage  
       FROM BABMwork8_22_1210_0012.dbo.concessions   
       WHERE concession = 'TH-003853199' 

```

and the stored procedure is,
EDIT in query

```
    ALTER PROCEDURE status_in_other_db
                    @other_db VARCHAR(50),
                    @concession VARCHAR(30)
        AS
    BEGIN
            SET NOCOUNT ON

        DECLARE @query VARCHAR(8000),
                @other_db_con VARCHAR(80),
                @source VARCHAR(10),
                @stage VARCHAR(50),
                @status_in_other_db  VARCHAR(50)

        SET @other_db_con = @other_db + '.dbo.concessions' ;
        SET @query = ('SELECT @source = source,@stage = stage FROM ' + @other_db_con + ' WHERE concession = '''+@concession+''' ')
            EXEC(@query)
        IF(@source!='PARK')
    BEGIN
        SET @status_in_other_db = 
        CASE @stage
            WHEN 'Open' THEN 'Open'
            WHEN 'Archive' THEN 'Archived'
            WHEN 'R4invoicing' THEN 'Ready for invoicing'
            WHEN 'N2Bdetermined' THEN'SplitBA'
            WHEN 'Invoiced' THEN 'Invoiced'
            WHEN 'Exclusion' THEN'Exclusion'
        END
    END
ELSE IF(@source='PARK')
    SET @status_in_other_db = 'PARK'
ELSE IF(@source='')
    SET @status_in_other_db = 'Not present'

SELECT @status_in_other_db as status_in_other_db
END

```

Here i want the value of @status\_in\_other\_db to be returned back, which should be my output parameter.

When executing this stored procedure, i am getting error:- @source scalar variable must be declared. 

Eventhough i modified as so, now i am not getting any error message nor the results...

```
SET @query  = 'DECLARE @source VARCHAR(10),  @stage VARCHAR(50) '

SET @query  = @query  + 'SELECT @source = source,@stage = stage 
    FROM BABMwork8_22_1210_0012.dbo.concessions 
    WHERE concession = ''TH-003853199'' '

```

please help me