从动态SQL查询中选择字段名 [英] SELECT fieldnames FROM dynamic SQL query

问题描述

我有一个存储过程，该过程使用多个参数来构建动态查询，然后执行该查询。该查询工作正常，但是，此过程将是Crystal Report的数据源，该报表需要静态 SELECT，且其字段名称可以引用。从Visual Basic应用程序调用Crystal Report，并从应用程序获取传递给它的参数。依次将参数传递给SQL Server存储过程。

I have a stored procedure that uses several parameters to build a dynamic query, which I execute. The query works fine, however, this procedure will be the data source for a Crystal Report which needs a "static" SELECT with field names it can reference. The Crystal Report is called from a Visual Basic application, and gets it's parameters passed to it from the application. It, in turn, passes the parameters to the SQL Server stored procedure.

我一定需要

SELECT fieldname1, fieldname2 
FROM Exec(@MydynamcSQL)

在我构建 @MydynamcSQL 。它是一个复杂的应用程序，它根据年份访问特定的表，并根据用户访问特定的数据库。我对SQL还是很陌生，所以也许我可以使用其他一些我不知道的方法？

after I build @MydynamcSQL. It is a complicated application accessing specific tables based on year, and specific databases based on the user. I am pretty new to SQL, so maybe there are other methods I could use that I am unaware of?

推荐答案

尝试创建一个临时表以临时插入数据，然后从该表中选择：

Try creating a temporary table to insert the data temporarily, then select from that table:

DECLARE @MydynamcSQL varchar(1000);

SET @MydynamcSQL = 'select fieldname1, fieldname1 from table1';

CREATE TABLE #Result
(
  fieldname1 varchar(1000),
  fieldname2 varchar(1000)  
)
INSERT #Result Exec(@MydynamcSQL)
SELECT fieldname1, fieldname1 -- here you have "static SELECT with field names"
FROM #Result 
DROP TABLE #Result

这篇关于从动态SQL查询中选择字段名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！