Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
450 views
in Technique[技术] by (71.8m points)

sql server - 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,

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...