I have an existing stored procedure which takes the Source and Target table names as a Varchar argument and does some complex logics. My requirement is to use the Source table’s column1 and with that value build a concatenated output. Ex.,

TableA
Column1
090912
899823
687678

I am taking only the first 2 digits and concatenating it to create an output like, ‘09,89,68’. This is used for other calculation.

@AllCode being the varchar(max) variable, I added the below select statement which works fine, if the table name is directly given. Now, I am trying to replace the TableA with the input argument which is a varchar. If I use a variable, then I should use the Exec statement. This is where I am struggling to include the @allCode variable assignment.

The is line works fine with actual table name:
‘select @AllCode = @AllCode + ‘ ‘ + Code from (select distinct left(Column1,2) as Code from TableA where TableA.Processedflag is null) tmp’

print @AllCode –> 09,89,68

I wrote the below code. assigned the subquery from the above line and added it to the @SqlQuery variable. I am not sure how to extend this query and use the Exec to include the @AllCode variable assignment.

alter procedure temp.spSample
@SrcTbl varchar(100),
@TgtTbl varchar(100)
as
Begin
Declare @AllCode varchar(500);
Declare @SqlQuery varchar(max);

set @SqlQuery = 'select distinct left(Column1,2) as Code from ' + @SrcTbl + ' where ' + @SrcTbl  +'.ProcessedFlag is null';
exec(@SqlQuery);
end

How to achieve the above without a Table Variable? I cannot use a Table variable as it is required to be created outside of the SP.

I tried this with having a Table variable and storing the values in the Table variable. But, In the same Stored Procedure i am not able to use the CREATE table variable and using the table variable in the same procedure. I get a table not found issue. It will work fine if the Table is created outside, but I dont want to create it outside due to maintenance issue. Any suggestion, how can I get the concatenated values @allCode as said in the above example? Can it be done without a table variable?