CODEWITHSUNDEEP
sqlsql-server
Filipe YaBa Polido
Filipe YaBa Polido

Reputation: 1674

Creating a SQL statement based on existing table fields

Having a table populated with databases and table names, is it possible to create some dynamic query based on those fields? Something like:

DECLARE @mysearch NVARCHAR(MAX)= 'SELECT db_name, tbl_name FROM UserConfig where ID = 1';

SELECT * FROM [db_name]..[tbl_name]

Being db_name and tbl_name the result values of @mysearch query.

For clarification, database and table names from where I need to select data are in a table, not hardcoded.

Example:

SELECT * FROM MYDB..MyTable

But "MYDB" and "MyTable" are values stored somewhere else.

Thanks.

Upvotes: 0

Views: 74

Answers (1)

Thom A
Thom A

Reputation: 95620

I am reading a lot through the lines here, but are you actually after this?

DECLARE @SQL nvarchar(MAX);
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SELECT @SQL = N'SELECT *' + @CRLF +
              N'FROM ' + QUOTENAME(db_name) + N'.dbo.' + QUOTENAME(tbl_name) + N';'
FROM UserConfig
WHERE ID = 1;

EXEC sp_executesql @SQL;

This assumes a single row would be returned for a single value of ID in the table UserConfig.

Upvotes: 1

Related Questions