Dynamic from statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic from statement

Author

Topic

tperry78
Starting Member

2 Posts

Posted - 2009-03-15 : 09:50:31

Hi,

I'm trying to cut down on the number of queries that are going to be running in a web application on one of our site, and am now stuck.

The system we use uses a data_id to identify assets within various database tables. all of these tables are effectively the same, but with one or two extra fields here and there, and are called the same apart from a number at the end. So we have - for simplicity - table_1, table_2.....table_16.

These numbers are obtained from another table in the system, which is passed the data_id from above.

ow, my question is this....

does anyone know if it is possible and what th syntax is for something like:

select field1, fieldb
from table_(select componentID from tblComponents where data_id =@data_id)

unsurprisingly i get an error. have tried building up a string with the above and then execing that and is parses compilation, but then fails on the exec as it can't find the table.

any ideas?

i just want to void having to do this in code, and having to run two queries, as the tables are 50k and 25k in places

TIA

Tony

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-15 : 11:03:10

Yeah, you can't be too dynamic in your from clause unfortunately. 2 approaches that may work.
First you could create dynamic sql. Downside is you don't get an execution plan with that approach so if this is a frequently used process or returns a lot of data it may not be the way to go.

Second you could create a main sp that evaluates a parameter and then calls the right code. Example below. This is better, still lacks an exec plan but is SP based which is better for security than dynamic sql. A little more of pain to set up though.

Create table #temp1(temp1field varchar(20))
Create table #temp2(temp2field varchar(20))

insert into #temp1 select 'temp1'
insert into #temp2 select 'temp2'

declare @tablenum tinyint
declare @mysql nvarchar(100)
declare @tablename varchar(20)='#temp'

select @tablenum=1
select @tablename +=cast(@tablenum as varchar(3))
select @mysql='select * from ' + @tablename
exec sp_executesql @mysql

Create procedure testProc
@tablenum tinyint
as

if @tablenum=1
begin
select * from tablename1
END

If @tablenum=2
begin
select * from tablename2
END

Mike
"oh, that monkey is going to pay"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-16 : 00:36:19

Also read www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

tperry78
Starting Member

2 Posts

Posted - 2009-03-16 : 04:32:02

Thanks guys. I suspected it wasn't possible but always worth asking. Madhivanan, thanks for the article...will give me something to read later

Tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources