This article will explain how to send an entire data-table to Stored Procedure as an Input parameter.
Passing a data-table to Stored Procedure is very helpful while improving the performance. For example, we can take a scenario, we have some no of records in a data-table and we need to call some stored procedure for all the records in the data-table. If we are going to call the stored procedure for each and every records, it will try to open and close the SQL Connection each time, which will degrade the system performance.
So the best solution is to pass the data-table itself to the stored procedure and process it inside the stored procedure instead of calling it again and again. It will improve the system performance also.
Here is the code to pass the data-table to the Stored procedure
--CREATE A
TABLE
CREATE TABLE #EMP_DETAILS
(
EMP_NAME VARCHAR(20),
EMP_ID VARCHAR(20)
)
--INSERT SOME
DATA INTO TABLE
INSERT INTO #EMP_DETAILS VALUES('AAA','AAA-123')
INSERT INTO #EMP_DETAILS VALUES('BBB','BBB-123')
INSERT INTO #EMP_DETAILS VALUES('CCC','CCC-123')
INSERT INTO #EMP_DETAILS VALUES('DDD','DDD-123')
INSERT INTO #EMP_DETAILS VALUES('EEE','EEE-123')
--CREATE THE
TABLE DATA TYPE:
CREATE TYPE USER_DEFINED_TABLE_TYPE AS
TABLE
(
--DEFINE TABLE STRUCTURE HERE
EMP_NAME VARCHAR(20),
EMP_ID VARCHAR(20)
)
--CREATE THE
STORED PROCEDURE WHICH HAS THE TABLE VARIABLE AS THE PARAMETER
CREATE PROCEDURE HOW_TO_PASS_DT_TO_SP
(
@TableVar USER_DEFINED_TABLE_TYPE READONLY
)
AS
BEGIN
SELECT * FROM @TableVar
END
NOW WE CAN RUN
OUR STORED PROCEDURE AND GET THE RESULT:
--DECLARE A VARIABLE OF DEFINED TABLE TYPE
DECLARE
@INPUTTABLE AS USER_DEFINED_TABLE_TYPE
--INSERT THE
RECORDS INTO TABLE VARIABLE FROM THE TABLE
INSERT INTO @INPUTTABLE SELECT
* FROM
#EMP_DETAILS
--OR YOU CAN
INSERT VALUES MANUALLY TO THE TABLE VARIABLE
INSERT INTO @INPUTTABLE SELECT
'FFF', 'FFF-123'
--EXECUTE THE
STORED PROCEDURE
EXEC
HOW_TO_PASS_DT_TO_SP @InputTable
OUTPUT:
v. helpful
ReplyDelete