Using a variable value as an object name

Question

Using a variable value as an object name

AMWild

Grasshopper

Points: 10
More actions
July 22, 2003 at 2:18 am

#101580

I am self-taught in T-SQL, so I may be being a bit stupid...
Anyway,
I need to be able to use the value of a variable as the name of an object. I am trying to create a stored procedure that can compare the value of one variable with the value of some (specified) or all fields (of whatever datatype) in a row of a table or query, without knowing what table or and field(s) will be specified at design time.
I currently do this in VB/VBA using ADO with an Tablevariable.Open, TableVariable.Fields(fieldname/ID).Value structure, but it is too slow or not possible in some cases. Does anyone know how I can accomplish this in T-SQL?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

You need to look at using a dynamic SQL statement where you build the statement as a varchar (string) and then execute it.

declare @sql nvarchar(4000)

set @sql = 'select * from Employees'

exec sp_executesql @sql

You should be able to run this against the Northwind database to see the results.

NPeeters SSChampion Points: 12536 More actions · Answer 2

Using the dynamic SQL Statement is the only solution.

Be careful when using this technique, since the execution plan of the statement will not be cached. This will impact the performance of the query you are running.

The only other way is to create separate procedures for each and every combination of parameters you are passing in. This will increase management effort, but it can give you a substantial performance gain.

jwt Say Hey Kid Points: 668 More actions · Answer 3

There is one option that may speed up your queries. Write all the sprocs for each possible variable combination (or the most commonly used ones.) These can be cached.

Now write a sproc that choses the right sproc to use based on the selection criteria (or do that portion on the VB side.)

Jeff

paulhumphris Hall of Fame Points: 3496 More actions · Answer 4

By using parameterised calls to sp_executesql, it is possible for the query optimiser to generate reusable query plans. (See BOL).

AMWild Grasshopper Points: 10 More actions · Answer 5

Can anyone see any problems with this idea:

Create a dynamic sql statement at run-time using sp_executesql that creates a temporary view (a Create View statement) - thus with a stored execution plan - that is accessed as necessary by the stored procedure, then deleted after use.