How can I run a query when both the column name and table name are passed in?

Question

Given a table name and column name in a pair of variables, can I perform a select query without using dynamic sql?

for example, I'd like something nicer than this:

CREATE PROCEDURE spTest (@table NVARCHAR(30), @column NVARCHAR(30)) AS

  DECLARE @sql NVARCHAR(2000) 
  SELECT @sql = N'SELECT ' + @column + N' FROM ' + @table
  PRINT @sql

  EXEC sp_executesql @sql

I'd like to do this because my dynamic sql version is 3x slower than the non-dynamic version (which doesn't support a programmable table/column name, hence this question).

Answer 1

So it sounds like the question is whether you can build and run a query at runtime without using dynamic SQL?

I'd say the answer is no. The choices are:

• dynamic SQL - calling sp_ExecuteSQL or Exec() given a user-built string. Whether that's pass-through SQL using an ADO library and a Command object, or it's right within a sproc.
• compiled SQL - using the usual objects (sprocs and UDF) with known good statements interacting with known good objects. Parsing is done beforehand in this case.

Answer 2

The dynamic version is going to be 3x slower, solely because your'e going to be swapping in and out the table name, and the parser can't optimize for that.

You might want to use a nested switch statement of sorts in your routine and use that to select your SELECT statement from pre-defined tables/columns, especially if your schema won't be changed frequently. That should run lots faster, but you'll lose the true dynamic-ness.