Dynamic SQL Result INTO #Temp Table

问题:

I have to Execute a dynamic SQL SELECT Query And put Results into a #TempTable.

DECLARE @StateId CHAR(3)='StateID';  
DECLARE @DeptId CHAR(15)='DeptID';  
DECLARE @Query VARCHAR(MAX)='Select Columns With Joins Passed From Front End'  
DECLARE @Where VARCHAR(500);  
DECLARE @FinalQuery VARCHAR(MAX)='';  
SET @Where='Some Where Condition';  
SET @FinalQuery='SELECT '+@Query+' '+@Where+''  
EXEC(@FinalQuery) -- Want To INSERT THIS Result IN SOME `#TempTable` 
                  -- SO that I can perform Some Operations On `#TempTable`

ALSO No Of columns returned From Dynamic SQL SELECT Are Dynamic.

Thanks,

回答1:

try the below example

DECLARE @StateId CHAR(3)='StateID';  
DECLARE @DeptId CHAR(15)='DeptID';  
DECLARE @Query VARCHAR(MAX)='*'  -- here you pass your query
DECLARE @Where VARCHAR(500);  
DECLARE @FinalQuery VARCHAR(MAX)='';  
SET @Where='from tablename where condition';  
SET @FinalQuery='SELECT '+@Query+' INTO #temptablename '+@Where;
EXEC(@FinalQuery) 

Note :If you need to use temtable after sp execution then use ## rather than # then we can access it or we can use persistent temporary table

回答2:

Please make sure that your query statements is correctly. Try this in your stored procedure and check the output results ( copy the result and execute it)

....
SET @FinalQuery='SELECT '+@Query+' '+@Where+'    
PRINT(@FinalQuery)
....

You can use the SQL Profiler tool to debug

回答3:

Try this... First create the temp table which you need for further calculation.

CREATE TABLE #TEMP ( STATEID INT, DEPTID INT )

DECLARE @STATEID CHAR(10)='STATEID';  
DECLARE @DEPTID CHAR(15)='DEPTID';  
DECLARE @QUERY VARCHAR(MAX)='INSERT INTO #TEMP_TABLE 
                             SELECT COLUMNS WITH JOINS PASSED FROM FRONT END'  
DECLARE @WHERE VARCHAR(500);  
DECLARE @FINALQUERY VARCHAR(MAX)=''  
SET @WHERE='SOME WHERE CONDITION'

SET @FINALQUERY=+@QUERY+' '+@WHERE+''  -- REMOVED SELECT 
EXEC(@FINALQUERY)