CODEWITHSUNDEEP
sqlsql-serverstored-procedures
m4rc
m4rc

Reputation: 3006

Transact SQL result of query used in another

I need to get the table name to query from a table.

var tableName = "select tableName from tableList where type='A';"

Then subsequently use that table name in another query.

"select * from" + tableName

Transact SQL/stored procedures are new to me in general so any help would be appreciated. I didn't design the database and unfortunately can't really change it to be a better design as much as I would love to!

My question is - is this possible from one stored procedure and if so can anyone mock up how I'd do it. Or if there are any better ways anyone can think of (bar redesigning the database!)

Thanks

Upvotes: 0

Views: 921

Answers (4)

Ali
Ali

Reputation: 2025

You can directly write 

select * from (select tableName from tableList where type='A') X

Upvotes: 0

kevchadders
kevchadders

Reputation: 8335

Maybe via dynamic SQL

DECLARE @String AS VARCHAR(8000)
DECLARE @TableName AS VARCHAR(50)
DECLARE @Results AS VARCHAR(8000)

SET @TableName = (select top 1 tableName from tableList where type='A')
SET @String = 'Select * from ' + @TableName 
SET @Results = @String + @TableName

EXEC @Results

Upvotes: 2

Isaac A. Nugroho
Isaac A. Nugroho

Reputation: 206

You should use dynamic SQL to achieve that. Basically, you execute your query using sp_executesql or exec, and store the result to a, kinda, staging table to be processed further:

declare @sql = varchar(8000);
select @sql = 'insert into resulttbl select * from ' + @tableName;
exec sp_executesql(@sql);
-- further process using resulttbl

Or

insert into resulttbl 
exec ('select * from ' + @tableName);
-- further process using resulttbl

Anyway, you should read the following article for a better explanation: The Curse and Blessings of Dynamic SQL

Upvotes: 0

Ocaso Protal
Ocaso Protal

Reputation: 20247

You can either use execas @kevchadders suggested or you can use sp_executesql, read The Curse and Blessings of Dynamic SQL for an excellent explantion on dynamic SQL.

Upvotes: 1

Related Questions