Stored Proc - cannot use variable

Database
Enthusiast

Stored Proc - cannot use variable

I have mutiple instances that load data into different tables based on the instance. so i want to make the table name dynamic by making it a variable

 

I declared the variable

 

declare table_name varchar(10);

 

select tab into table_name from control_table where instance=in_instance;

 

I used the variable in the sp below as

 

select * from XYZ.table_name. -- 0 rows selected

if i repalce the variable with name, it returns rows

select * from XYZ.test_table; - 1 row selected

 

The sp also compiles fine but it doesnt fetch any data or throw error. am i missing any syntax? Please suggest. Thanks!


Accepted Solutions
Junior Contributor

Re: Stored Proc - cannot use variable

To make a table name dynamic you need Dynamic SQL, i.e. EXECUTE IMMEDIATE.
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Stored Proc - cannot use variable

To make a table name dynamic you need Dynamic SQL, i.e. EXECUTE IMMEDIATE.
Enthusiast

Re: Stored Proc - cannot use variable

Looks like there is a limit on dynamic sql, it way beyond 35k limit. @dnoeth