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

问题描述

我有一个存储过程，它使用多个参数来构建我执行的动态查询.查询工作正常，但是，此过程将成为 Crystal Report 的数据源，它需要一个可以引用的静态"SELECT with field names.Crystal Report 从 Visual Basic 应用程序中调用，并获取从应用程序传递给它的参数.它反过来将参数传递给 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屋！