如何传入列名和表名时，如何运行查询？

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

给定一对变量中的表名和列名,我可以在不使用动态sql的情况下执行select查询吗?

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).

我想这样做是因为我的动态sql版本比非动态版本慢3倍(它不支持可编程的表/列名称,因此这个问题)。

2 个解决方案

#1

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.

动态版本将慢3倍,完全是因为你要交换表名,而解析器无法优化。

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.

您可能希望在例程中使用排序的switch语句,并使用它从预定义的表/列中选择SELECT语句,尤其是在不经常更改架构的情况下。那应该跑得快得多,但你会失去真正的动态。

#2

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

所以听起来问题是你是否可以在运行时构建和运行查询而不使用动态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.

动态SQL - 在给定用户构建的字符串的情况下调用sp_ExecuteSQL或Exec()。是否是使用ADO库和Command对象的传递SQL,或者它在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.

编译SQL - 使用通常的对象(sprocs和UDF)与已知好的语句交互已知的好对象。在这种情况下,预先进行解析。

#1