Click here to Skip to main content
14,454,243 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to get the data from tables for the user specified columns.

What I have tried:

--dummy table
CREATE TABLE #tbEmployeeMaster (EmployeeId INT IDENTITY(1,1), EmployeeName VARCHAR(100), FatherName VARCHAR(100), MotherName VARCHAR(100), Age INT)

INSERT INTO #tbEmployeeMaster (EmployeeName ,FatherName , MotherName, Age) VALUES ('Aman', 'Rakesh', 'Anita', 22)
INSERT INTO #tbEmployeeMaster (EmployeeName ,FatherName , MotherName, Age) VALUES ('Sunil', 'Sandeep', 'Kavita', 22)


DECLARE @SelectedColumns VARCHAR(500)='EmployeeName,Age';
--Note: User can select above columns in any order e.g. Age,EmployeeName

--I tried dynamic sql as follows.
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT ' + @SelectedColumns + ' FROM #tbEmployeeMaster'
EXEC(@SQL) --it works

--I want to insert the data into temporary table using the syntax below so that i can use that further.But IT did not work. I know table varible can not be used here because we don't know the column order.

SELECT * INTO #tbEmp EXEC(@SQL)
Posted
Updated 22-May-18 5:29am
v2
Comments
PIEBALDconsult 22-May-18 8:32am
   
INSERT ... EXECUTE ... ?
Rate this:
Please Sign up or sign in to vote.

Solution 1

You can't use a table variable anyway because of the scope of the procedures.

You have to add the 'select into' into the query:

DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT ' + @SelectedColumns + ' INTO #tbEmp FROM #tbEmployeeMaster' 
EXEC(@SQL)


using select-into self defines the table structure, as i'm sure you already know.
   
Comments
xpertzgurtej 22-May-18 23:59pm
   
did not work..got error : Invalid object name '#tbEmp'
Rate this:
Please Sign up or sign in to vote.

Solution 2

I'm afraid your solution will not work as you envision. You will need to create the #tbEmp /schema ahead of time before executing the Stored Procedure. Or use the OPENROWSET function. or write the whole solution using dynamics SQL .

CREATE TABLE #tbEmp (EmployeeName VARCHAR(100), Age INT)
...
INSERT INTO #tbEmp EXEC(@SQL)


sql - Insert results of a stored procedure into a temporary table - Stack Overflow[^]

https://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100