No, 'fraid not ...
You would have to use Dynamic SQL - that is to say construct a string variable containing the whole SQL, combining your @Database variable with the rest of the SQL, and then execute that.
If you need to use a variable for Database name, Table name, Column name etc. then you have to use Dynamic SQL.
If you want to COMPARE the data IN a Column to a Variable that's fine using a regular SELECT statement ...
Dynamic SQL is more of a pain to debug, because you can't easily see what is going on. It will be a lot more difficult if you need to use a Cursor (in general terms I suggest you try to avoid a cursor, and loops, SQL operates much more effectively if you can operate on "Sets" of data)
If you have to go down the Dynamic SQL route my suggestion would be:
- Create the @MySQL string of SQL (i.e. concatenating your SQL statement with your @Database etc. variable)
- Use PRINT or SELECT to output the generated SQL
- Try executing that SQL yourself, manually
You can do:
BEGIN TRANSACTION
... your test SQL Cut & Pasted here ...
ROLLBACK
so that there are no side effects (obvious, or "hidden"!!)
- Adjust the test SQL until it is right, and then put those changes back into the original code that generated the SQL, then Rinse & Repeat from #2
- Once the code is correctly generating valid SQL then change the PRINT / SELECT to
EXEC (@MySQL)
Your cursor appears to be just selecting INDCODE, INDNAME & TABLENAME outputting one row per iteration of the cursor?? It would be much (as in Much MUCH!!) better to SELECT it as a single select for all matching rows in the table rather than RBAR (Row By Agonising Row). The APP can then loop round the multi-row resultset "processing" each row. ("Processing" might just be "displaying")
Then perhaps Loop to select a different Database to query.