Alex Rivera | Logout

Evaluate in T-SQL

Asked 2009-03-27T03:27:04.290
9

I've got a stored procedure that allows an IN parameter specify what database to use. I then use a pre-decided table in that database for a query. The problem I'm having is concatenating the table name to that database name within my queries. If T-SQL had an evaluate function I could do something like

eval(@dbname + 'MyTable')

Currently I'm stuck creating a string and then using exec() to run that string as a query. This is messy and I would rather not have to create a string. Is there a way I can evaluate a variable or string so I can do something like the following?

SELECT *
FROM eval(@dbname + 'MyTable')

I would like it to evaluate so it ends up appearing like this:

SELECT *
FROM myserver.mydatabase.dbo.MyTable
sql-server-2005 t-sql evaluation
Edit
Report

3 Answers

16

Read this... The Curse and Blessings of Dynamic SQL, help me a lot understanding how to solve this type of problems.

answered 2009-04-01T20:59:38.480
9

There's no "neater" way to do this. You'll save time if you accept it and look at something else.

EDIT: Aha! Regarding the OP's comment that "We have to load data into a new database each month or else it gets too large.". Surprising in retrospect that no one remarked on the faint smell of this problem.

SQL Server offers native mechanisms for dealing with tables that get "too large" (in particular, partitioning), which will allow you to address the table as a single entity, while dividing the table into separate files in the background, thus eliminating your current problem altogether.

To put it another way, this is a problem for your DB administrator, not the DB consumer. If that happens to be you as well, I suggest you look into partitioning this table.

answered 2009-03-27T03:40:26.507
2

You can't specify a dynamic table name in SQL Server.

There are a few options:

  1. Use dynamic SQL
  2. Play around with synonyms (which means less dynamic SQL, but still some)

You've said you don't like 1, so lets go for 2.

First option is to restrict the messyness to one line:

begin transaction t1;
declare @statement nvarchar(100);

set @statement = 'create synonym temptablesyn for db1.dbo.test;'
exec sp_executesql @statement

select * from db_syn

drop synonym db_syn;

rollback transaction t1;

I'm not sure I like this, but it may be your best option. This way all of the SELECTs will be the same.

You can refactor this to your hearts content, but there are a number of disadvantages to this, including the synonym is created in a transaction, so you can't have two of the queries running at the same time (because both will be trying to create temptablesyn). Depending upon the locking strategy, one will block the other.

Synonyms are permanent, so this is why you need to do this in a transaction.

answered 2009-04-01T11:16:40.393

Your Answer