Introduction
As we all know that the dynamic SQL is not good where performance is concern. But sometimes we don't have other choices. As I personally think that, no developer chooses the dynamic SQL with interest, but they choose it as they have no alternates to minimize the code. Well the debugging of dynamic SQL is not so easy.
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.
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;
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
