others - sql - 不创建临时表,如何存储动态查询的结果?

下面是我们的查询:


 DECLARE @ProcName VARCHAR(100)='spGetOraganizationsList',


 @ParamName VARCHAR(100),@DataType VARCHAR(20),


 @Query NVARCHAR(MAX)='EXEC '+'spGetOraganizationsList '



 SELECT PARAMETER_NAME,DATA_TYPE 


 INTO #Tmp


 FROM information_schema.PARAMETERS


 WHERE SPECIFIC_NAME=@ProcName



 DECLARE ParamCursor CURSOR 


 FOR SELECT * FROM #Tmp


 OPEN ParamCursor


 FETCH NEXT FROM ParamCursor


 INTO @ParamName,@DataType



 WHILE @@FETCH_STATUS = 0 


 BEGIN


 SET @Query=@Query+@ParamName+'=Null,'


 FETCH NEXT FROM ParamCursor INTO @ParamName,@DataType


 END


 CLOSE ParamCursor


 DEALLOCATE ParamCursor


 DROP TABLE #Tmp



 EXEC sp_executesql @Query



我无法将它的结果存储在一个临时表中,并且OPENROWSET不接受变量。

时间:

为什么不创建一个视图去替代创建和删除表。


CREATE VIEW"VIEW_NAME" AS"SQL Statement";



一个不相关的例子:-


CREATE VIEW V_Customer


AS SELECT First_Name, Last_Name, Country


FROM Customer;



你当然可以将存储过程的结果插入到临时表中:


CREATE PROCEDURE PurgeMe


AS


SELECT convert(int, 1) AS DaData


UNION


SELECT convert(int, 2)


GO



CREATE TABLE #Doodles (AnInteger int)



INSERT #Doodles EXECUTE PurgeMe



SELECT * FROM #Doodles



但是临时表的作用域会出现问题,你可能会发现在例程中无法看到创建的临时表。

解决作用域问题的方法是执行以下操作:

  • 创建最小的临时表(例如用一列)
  • 在例程中的TEMP表上使用ALTER TABLE,使它模式符合你的需求(这可能很棘手,但可以完成),
  • 将数据放入临时表
  • 从例程返回 - 调用例程现在可以访问临时表

使用全局临时表和动态OPENROWSET


 DROP TABLE ##Tmp;


 GO



 DECLARE @ProcName VARCHAR(100)='spGetOraganizationsList',


 @ParamName VARCHAR(100), @DataType VARCHAR(20),


 -- Mind to specify database and schema of the SP


 @Query NVARCHAR(MAX)=' EXEC [mydb].[dbo].spGetOraganizationsList ';


 SELECT PARAMETER_NAME,DATA_TYPE 


 INTO #Tmp


 FROM information_schema.PARAMETERS


 WHERE SPECIFIC_NAME=@ProcName;



 -- Build SP exec



 DECLARE ParamCursor CURSOR 


 FOR SELECT * FROM #Tmp


 OPEN ParamCursor


 FETCH NEXT FROM ParamCursor


 INTO @ParamName,@DataType



 WHILE @@FETCH_STATUS = 0 


 BEGIN


 SET @Query=@Query+@ParamName+'=Null,'


 FETCH NEXT FROM ParamCursor INTO @ParamName,@DataType


 END


 CLOSE ParamCursor


 DEALLOCATE ParamCursor


 SET @Query = left(@Query, len(@Query) - 1);



 -- Build ad hoc distributed query which creates ##Tmp from SP exec.



 SET @Query = 'SELECT * INTO ##Tmp FROM OPENROWSET(''SQLNCLI'', ''Server=localhost;Trusted_Connection=yes;'',''' + @Query + ''')';



 EXEC (@Query);



 -- Created by dynamic sql `##Tmp` is availabe in the current context. 


 SELECT *


 FROM ##Tmp;



不要忘记先启用hoc分布式查询。


sp_configure 'Show Advanced Options', 1


GO


RECONFIGURE


GO


sp_configure 'Ad Hoc Distributed Queries', 1


GO


RECONFIGURE


GO



编辑

我的回答只解决了一个问题,将动态proc调用的结果存储在临时表中,但是还有更多问题。

首先,如果@p的类型是用户定义表类型,那么@p=null就不会编译,你需要 declare @t myType; exec mySp ... ,@p=@t ...

...