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
