Introduction
Some times when the developer works with stored procedure they want to make certain dynamic SQL and want to copy the output or the result set of the dynamic SQL into a table and next want to work with table for farther processing.
Please note that the Temporary Table is not permitted here.
Please note that the Temporary Table is not permitted here.
The Problem
As the columns name and data type of the dynamic SQL is dynamic, it is not possible to make a table definition within the stored procedure.
To understand it properly here I am providing an example.
-- Base Table Object Creation
IF OBJECT_ID('my_EMP') IS NOT NULL
BEGIN
DROP TABLE my_EMP;
END
GO
CREATE TABLE my_EMP
(EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
EMPDEPT VARCHAR(50) NOT NULL,
EMPCITY VARCHAR(50) NOT NULL,
EEMSTATE VARCHAR(50) NOT NULL);
GO
-- Inserting some Records
INSERT INTO my_EMP
(EMPNAME, EMPGRADE, EMPGRADE, EMPDEPT, EMPCITY, EEMSTATE)
VALUES ('JOYDEEP DAS', 'B', 'DEV', 'AGARTALA', 'TRIPURA'),
('TUHIN SHINAH', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL'),
('SANGRAM JIT', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL'),
('SUKAMAL JANA', 'B', 'DEV', 'CHUCHURA', 'WEST BENGAL'),
('SUDIP DAS', 'A', 'MGR', 'KOLKATA', 'WEST BENGAL'),
('SAIKAT SREE', 'A', 'MGR', 'KOLKATA', 'WEST BENGAL'),
('MANI SANKAR', 'C', 'DM', 'AGARTALA', 'TRIPURA'),
('A DEKA', 'C', 'DM', 'GWAHATI', 'ASSAM');
GO
-- Creting Stored Procedure with Dynamic SQL
-- Note that the parameters, one is the fields name and second is the Table objects
-- So the Dynamic Sql fields and table are dynamic and depends on user input.
IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL
BEGIN
DROP PROCEDURE usp_DISPLAYEMP;
END
GO
CREATE PROCEDURE usp_DISPLAYEMP
(
@p_Attributes VARCHAR(200) = NULL,
@p_TblName VARCHAR(50) = NULL
)
AS
DECLARE @sqlString VARCHAR(200);
BEGIN
IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''
BEGIN
SET @sqlString='SELECT '+ @p_Attributes + ' FROM '+ @p_TblName;
EXEC (@sqlString);
END
ELSE
BEGIN
PRINT 'Input can not be Null';
END
END
-- Executing the SP [ with Fields name EMPNAME, EMPGRADE, EMPGRADE ]
EXEC usp_DISPLAYEMP
@p_Attributes = 'EMPNAME, EMPGRADE, EMPGRADE',
@p_TblName = 'my_EMP'
The output is
EMPNAME EMPGRADE EMPGRADE
-------------------------------------------------- -------- --------
JOYDEEP DAS B B
TUHIN SHINAH B B
SANGRAM JIT B B
SUKAMAL JANA B B
SUDIP DAS A A
SAIKAT SREE A A
MANI SANKAR C C
A DEKA C C
(8 row(s) affected)
-- Executing the SP [ with Fields All Fields Name ]
EXEC usp_DISPLAYEMP
@p_Attributes = '*',
@p_TblName = 'my_EMP'
The Output is
EMPID EMPNAME EMPGRADE EMPDEPT EMPCITY EEMSTATE
----------------------------- ---------------------------------------
1 JOYDEEP DAS B DEV AGARTALA TRIPURA
2 TUHIN SHINAH B DEV KOLKATA WEST BENGAL
3 SANGRAM JIT B DEV KOLKATA WEST BENGAL
4 SUKAMAL JANA B DEV CHUCHURA WEST BENGAL
5 SUDIP DAS A MGR KOLKATA WEST BENGAL
6 SAIKAT SREE A MGR KOLKATA WEST BENGAL
7 MANI SANKAR C DM AGARTALA TRIPURA
8 A DEKA C DM GWAHATI ASSAM
(8 row(s) affected)
So in this example the output of the stored procedure depends on user input. We can't understand how many columns it displays.
We want to use a table within this stored procedure.
Easy Solutions
Example-1
-- Using Temp Table
IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL
BEGIN
DROP PROCEDURE usp_DISPLAYEMP;
END
GO
CREATE PROCEDURE usp_DISPLAYEMP
(
@p_Attributes VARCHAR(200) = NULL,
@p_TblName VARCHAR(50) = NULL
)
AS
DECLARE @sqlString VARCHAR(200);
BEGIN
IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''
BEGIN
SET @sqlString='SELECT '+ @p_Attributes +
' INTO TMP_TBL FROM '+@p_TblName;
' INTO TMP_TBL FROM '+@p_TblName;
EXEC (@sqlString);
END
ELSE
BEGIN
PRINT 'Input can not be Null';
END
END
Example-2
-- Using Temp Table
IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL
BEGIN
DROP PROCEDURE usp_DISPLAYEMP;
END
GO
CREATE PROCEDURE usp_DISPLAYEMP
(
@p_Attributes VARCHAR(200) = NULL,
@p_TblName VARCHAR(50) = NULL
)
AS
DECLARE @sqlString VARCHAR(200);
BEGIN
IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''
BEGIN
SET @sqlString='SELECT '+ @p_Attributes +
' INTO ' + TMP_TBL +
' FROM '+ @p_TblName;
EXEC (@sqlString);
END
ELSE
BEGIN
PRINT 'Input can not be Null';
END
END
Share your Knowledge
If we can put the output in a Temporary Table (the Local Temporary Table) it well is the BEST.
But in this situation it is NOT possible. If have you an idea, please share your knowledge with us.
Related Tropics
Dynamic SQL With sp_executesql ()
Hope you like it.
Posted by: MR. JOYDEEP DAS
Yes we can use it using temporary table as well as normal table.... Remember we often face the question "How can we copy data or without data from a table to another table" ...
ReplyDeleteIt is a very common question in any Sql Server interviwe question. Now the answer of this problem also lie in this solution.
here is the Solution....
DECLARE @sqlString NVARCHAR(200)
DECLARE @Attribute NVARCHAR(200)
DECLARE @TableName NVARCHAR(20)
SET @Attribute='EmployeeId,EmployeeName,EmployeeCode'
SET @TableName='Employee'
IF OBJECT_ID('##Emp_Temp2') IS NOT NULL
BEGIN
DROP Table ##Emp_Temp2
SET @sqlString='SELECT '+@Attribute+ '
Into ##Emp_Temp2 FROM '+ @TableName
EXEC (@sqlString)
SELECT * FROM ##Emp_Temp2
END
GO
go
Hello “Saikat Mitra”
DeleteThanks for your interest and comments.
But the global Temporary table that I don’t needed here. Actually I am looking for Local Temporary Table (with single #). As the global temporary table have its own complexity in multi user environment.
Yes I have examined it using the same code .
ReplyDeleteIn case of using local temporary table .SSMS give error msg invalid object..
that means when we execute " Select * from #Emp_Temp2" then #Emp_Temp2 already out of scope.
So, if we execute total statements in a single execute statement then it works...
Here is the code..
BEGIN
DECLARE @sqlString NVARCHAR(200)
DECLARE @Attribute NVARCHAR(200)
DECLARE @TableName NVARCHAR(20)
SET @Attribute='EmployeeId,EmployeeName,EmployeeCode'
SET @TableName='Employee'
--DECLARE @Emp_Temp2 TABLE
SET @sqlString='SELECT '+@Attribute+ ' Into #Emp_Temp2 FROM '+ @TableName+'; SELECT * FROM #Emp_Temp2'
EXEC (@sqlString)
--SELECT * FROM #Emp_Temp2
END
---So in this case we can use local temporary table..
Saikat,
DeleteThanks for you reply.
The approach you made is correct but you can't use the #Emp_temp2 within the Stored Procedure.
I want to be use this temporary table within the stored procedure.
Is there any implementation process of that?