Inserting Stored Procedure result into a variable or Temp table in Sql Server

Since stored procedures are generally the most efficient way to call and retrieve data. But some time we need to perform some operation on the result of the stored procedure. In this case we can’t perform our operation on the result of stored procedure. For overcoming such type of problem we need to use temp table or variable table.

Let’s see the example

Suppose I have a store procedure which gives the result of Employee details like (EmployeeKey, FirstName, LastName, MiddleName, HireDate, BirthDate, MaritalStatus, Gender, StartDate, EndDate, Status)

See the below Stored Procedure

CREATE PROCEDURE GetEmployeeDetails AS SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee] GO

Note: I am using AdventureWorksDW2008R2 Database

See the result.

 

Suppose our requirement is find the total experience based on the Hire Date and the age of the employee based on the Birth date.

For getting the desirer output we need to store the stored procedure result  in the temp table or variable table.

Schema Known

First we need to declare the variable table or temp table.

Variable table

DECLARE @varTable table ( [EmployeeKey] int ,[FirstName] nvarchar(50) ,[LastName] nvarchar(50) ,[MiddleName] nvarchar(50) ,[HireDate] date ,[BirthDate] date ,[MaritalStatus] nvarchar(50) ,[Gender] nvarchar(50) ,[StartDate] date ,[EndDate] date ,[Status] nvarchar(50) ) insert into @varTable exec GetEmployeeDetails select * from @varTable

See the result.

Now I am performing additional operation like getting the experience and age

DECLARE @varTable table ( [EmployeeKey] int ,[FirstName] nvarchar(50) ,[LastName] nvarchar(50) ,[MiddleName] nvarchar(50) ,[HireDate] date ,[BirthDate] date ,[MaritalStatus] nvarchar(50) ,[Gender] nvarchar(50) ,[StartDate] date ,[EndDate] date ,[Status] nvarchar(50) ) insert into @varTable exec GetEmployeeDetails select  [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,DATEDIFF(YYYY,[HireDate],GetDate()) as [year of experiance]       ,[BirthDate]       ,DATEDIFF(YYYY,[BirthDate],GetDate()) as [Age]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]  from @varTable

I have added two columns.

 

See the result.

 

Temp table

Similarly we store the result in temp table

create table #temptable ( [EmployeeKey] int ,[FirstName] nvarchar(50) ,[LastName] nvarchar(50) ,[MiddleName] nvarchar(50) ,[HireDate] date ,[BirthDate] date ,[MaritalStatus] nvarchar(50) ,[Gender] nvarchar(50) ,[StartDate] date ,[EndDate] date ,[Status] nvarchar(50) ) insert into #temptable exec GetEmployeeDetails select  [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]          ,DATEDIFF(YYYY,[HireDate],GetDate()) as [year of experiance]       ,[BirthDate]          ,DATEDIFF(YYYY,[BirthDate],GetDate()) as [Age]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]  from #temptable  drop table #temptable

We will get the same output