How to TABLE or TABLE variable to SP
I want to pass a TABLE or TABLE variable to my stored procedure? Can I do it? NO, SQL server does not support to pass a TABLE or TABLE variable as parameters of stored procedure. So how can I do it?
In this article I am trying to demonstrate the above scenario by using XML as the format to pass a table to a stored procedure.
We are going to transform the table or query result to an XML variable and pass to the stored procedure. We can either convert the XML parameters back to the TABLE variable or directly use the XQuery on the XML variable.
Let’s table a simple example to understand it.
“I have 2 table objects. One is Inventory table and it contains the item code and stock quantity. Another is Order details. When the order came in order details and when we run the stored procedure it always decrease the stock from inventory table by item wise.”
No first we create the Table definition
n Inventory Table
CREATE TABLE [dbo].[tran_Inventory]
INSERT INTO [dbo].[tran_Inventory]
GO
n Order Table
CREATE TABLE [dbo].[Ord_Details]
GO
INSERT INTO [dbo].[Ord_Details]
GO
Now Create the Stored procedure
CREATE PROCEDURE [dbo].[my_Proc]
UPDATE tran_Inventory SET
Execution the Stored Procedure
DECLARE @x XML
Observation the Result
SELECT * FROM tran_Inventory
Hope you like it.
I want to pass a TABLE or TABLE variable to my stored procedure? Can I do it? NO, SQL server does not support to pass a TABLE or TABLE variable as parameters of stored procedure. So how can I do it?
In this article I am trying to demonstrate the above scenario by using XML as the format to pass a table to a stored procedure.
We are going to transform the table or query result to an XML variable and pass to the stored procedure. We can either convert the XML parameters back to the TABLE variable or directly use the XQuery on the XML variable.
Let’s table a simple example to understand it.
“I have 2 table objects. One is Inventory table and it contains the item code and stock quantity. Another is Order details. When the order came in order details and when we run the stored procedure it always decrease the stock from inventory table by item wise.”
Please note that SQL Server 2008 is used for this example.
No first we create the Table definition
n Inventory Table
CREATE TABLE [dbo].[tran_Inventory]
(ITEMCD VARCHAR(20) NOT NULL PRIMARY KEY,
STOCK INT NULL)
GO
INSERT INTO [dbo].[tran_Inventory]
(ITEMCD, STOCK)
VALUES('A001', 1000), ('A002', 1000), ('A003', 1000)
GO
n Order Table
CREATE TABLE [dbo].[Ord_Details]
(
ORDID INT IDENTITY(1,1) NOT NULL,
ITEMCD VARCHAR(20) NOT NULL,
QTY INT NULL
)
GO
INSERT INTO [dbo].[Ord_Details]
(ITEMCD, QTY)
VALUES ('A001', 20), ('A002', 30), ('A003', 40)
GO
Now Create the Stored procedure
CREATE PROCEDURE [dbo].[my_Proc]
(
@param_x XML
)
AS
SET NOCOUNT ON
UPDATE tran_Inventory SET
stock = stock - v.Qty
FROM tran_Inventory inv
INNER JOIN (SELECT x.item.value('@ITEMCD[1]', 'VARCHAR(20)')AS ItemNumber,
x.item.value('@QTY[1]', 'INT') AS Qty
FROM @param_x.nodes('//items/item') AS x(item)
) v ON (v.ItemNumber = inv.ITEMCD)
RETURN
Execution the Stored Procedure
DECLARE @x XML
SET @x = (SELECT ITEMCD, QTY FROM Ord_Details FOR XML RAW('item'),ROOT('items'), TYPE)
EXECUTE my_Proc @x
Observation the Result
SELECT * FROM tran_Inventory
Hope you like it.
Table Variable with Dynamic SQL
Introductions
The table variable gives us the high degree of flexibility to work with T-SQL construct. Most of the developer uses the table variable to find the desired solutions due to its simplicities. Here in this article I am not discussing related to table variable. I already discuss it in my previous article (Please refer to the related tropics portions of the article).
Here I am trying to discuss about the Table variable with dynamic SQL constructs and how to use the table variable in the dynamic SQL.
Before writing this article, I saw several article related to it, but they are discussing about a specific example in mind. Here in this article I am trying to cover all the aspects related to Table variable and dynamic SQL.
Although I do not prefer any dynamic SQL constructs but depending on the criteria and implementations in mind we have to go to choose these options. With the dynamic SQL and Table variable, developers are facing lot of common problem. In this article I am trying to figure out those problems and try to explain the solution of it.
Focused area
The area that we focused in this article is mentioned bellow.
1. Table variable and Performance aspects
2. The Common problem that we faced
3. An Example of the Problem
4. How we can solve it
Table variable and Performance aspects
When we are making a SQL constructs we must think about the performance aspects of the query. So we must understand that the using Table variable is good for performance boost up or not. Before using Table variable, we must know some facts related to it.
ü MS SQL Server will not use parallelism when modifying a table variable.
The query used to insert rows into the table variable will not use parallelism.
The query used to insert rows into the table variable will not use parallelism.
ü MS SQL Server does not create any statistics, like histogram, for table variables, so
the estimates on your second query will be a rough guess.
the estimates on your second query will be a rough guess.
ü Use table variables for really low number of rows, and if possible use OPTION
(RECOMPILE) in our query so the query optimizer could know the number of rows in
the table variable by the time it executes the query.
(RECOMPILE) in our query so the query optimizer could know the number of rows in
the table variable by the time it executes the query.
ü If the number of rows is high than using temporary table is a good idea for
performance boost up as the temporary table use the Parallelism.
performance boost up as the temporary table use the Parallelism.
What is Parallelism?
MS SQL server can performs a query or index operations in parallel by using several operating system threats, the operation can be completed quickly and efficiently.
For more information related to Parallelism, follow the MSDN link.
The Common problem that we faced
Here I am mentioning an error message that most of the developer found if they made mistake when working with Table variable and dynamic SQL constructs.
Msg 137, Level 16, State 1, Procedure my_proc, Line 13
Must declare the scalar variable "@TBLVAR".
OR
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TBLVAR"
An Example of the Problem
Here is the example from where the Error message is generated.
Here is a simple SQL example where the Error occurred.
Example-1
DECLARE @my_TABLE VARCHAR(50)='my_STUDENT'
EXEC('SELECT * FROM @my_TABLE')
-- Error Messages
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@my_TABLE".
Example-2
In this example, the error message is generated when the stored procedure is compiled.
-- Base Table
IF OBJECT_ID('my_STUDENT') IS NOT NULL
BEGIN
DROP TABLE my_STUDENT;
END
GO
CREATE TABLE my_STUDENT
(ROLL INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SNAME VARCHAR(50) NOT NULL,
CLASS INT NOT NULL);
GO
-- Insert Records
INSERT INTO my_STUDENT
(SNAME, CLASS)
VALUES ('Joydeep', 1),
('Sukamal', 2),
('Sandip', 3),
('Samgram jit', 4)
GO
-- SP Example
IF OBJECT_ID('my_proc') IS NOT NULL
BEGIN
DROP PROCEDURE my_proc;
END
GO
CREATE PROCEDURE my_proc
(
@p_OpeningMood INT = 4
)
AS
DECLARE @SQLSTRING VARCHAR(100)
DECLARE @TBLVAR TABLE
(ROLL INT,
SNAME VARCHAR(50))
BEGIN
IF @p_OpeningMood = 4
BEGIN
SET @SQLSTRING = 'INSERT INTO '+ @TBLVAR
+' SELECT * FROM my_STUDENT'
EXEC (@SQLSTRING);
END
SELECT * FROM @TBLVAR;
END
GO
-- Error Messages
Msg 137, Level 16, State 1, Procedure my_proc, Line 13
Must declare the scalar variable "@TBLVAR".
In this example, the Error message is generated when the Stored Procedure is executed.
-- SP Example [Another Variations]
IF OBJECT_ID('my_proc') IS NOT NULL
BEGIN
DROP PROCEDURE my_proc;
END
GO
CREATE PROCEDURE my_proc
(
@p_OpeningMood INT = 4
)
AS
DECLARE @SQLSTRING VARCHAR(2000)
DECLARE @TBLVAR TABLE
(ROLL INT,
SNAME VARCHAR(50))
BEGIN
IF @p_OpeningMood = 4
BEGIN
SET @SQLSTRING = 'INSERT INTO @TBLVAR
SELECT * FROM my_STUDENT'
EXEC (@SQLSTRING);
END
SELECT * FROM @TBLVAR;
END
GO
-- Messages
Command(s) completed successfully.
-- Executing the Stored Proc
EXEC my_proc
@p_OpeningMood=4
-- Error Messages
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TBLVAR"
How we can solve it
We have to know, how to write the Dynamic SQL with Table variable to solve the problem.
First we look at the Example-1 and solve it (Passing table variable into dynamic SQL).
Please remember that the solutions of Example-1 is worked on MS SQL Server 2008 only
IF TYPE_ID('my_STUDENT') IS NOT NULL
BEGIN
DROP TYPE my_STUDENT
END
GO
CREATE TYPE my_STUDENT AS TABLE
(ROLL INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SNAME VARCHAR(50) NOT NULL,
CLASS INT NOT NULL);
GO
BEGIN
DECLARE @my_TABLE my_STUDENT
INSERT @my_TABLE VALUES ('Joydeep', 1),
('Sukamal', 2),
('Sandip', 3),
('Samgram jit', 4)
EXECUTE sp_executesql N'SELECT * FROM @my_TABLE',
N'@my_TABLE my_STUDENT READONLY',
@my_TABLE
END
GO
To passing the Table variable to a dynamic SQL please follow the gideline mentioned bellow.
[A] Defining the Table Type:
In my example, first I check that the type name exists or not. If exists than drop the type name.
In my example, first I check that the type name exists or not. If exists than drop the type name.
IF TYPE_ID('my_STUDENT') IS NOT NULL
BEGIN
DROP TYPE my_STUDENT
END
GO
CREATE TYPE my_STUDENT AS TABLE
(ROLL INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SNAME VARCHAR(50) NOT NULL,
CLASS INT NOT NULL);
GO
[B] Now declare a Variable by this Type name:
Here in this example, we are also filling some records in it.
DECLARE @my_TABLE my_STUDENT
INSERT @my_TABLE VALUES ('Joydeep', 1),
('Sukamal', 2),
('Sandip', 3),
('Samgram jit', 4)
In case of stored procedure
CREATE PROCEDURE my_proc
(
@my_TABLE my_STUDENT READONLY
)
AS
BEGIN
END
GO
[C] Passing the variable into Dynamic statement:
In my example
EXECUTE sp_executesql N'SELECT * FROM @my_TABLE',
N'@my_TABLE my_STUDENT READONLY',
@my_TABLE
For the stored procedure we can
DECLARE @DataTable AS my_STUDENT
INSERT INTO @DataTable SELECT * FROM (Some data)
-- Call the stored proc
EXECUTE my_proc @my_TABLE = @DataTable
Now we are going to solve the Example-2
We can solve it by
IF OBJECT_ID('my_proc') IS NOT NULL
BEGIN
DROP PROCEDURE my_proc;
END
GO
CREATE PROCEDURE my_proc
(
@p_OpeningMood INT = 4
)
AS
DECLARE @SQLSTRING VARCHAR(2000)
BEGIN
IF @p_OpeningMood = 4
BEGIN
SET @SQLSTRING = 'DECLARE @TBLVAR TABLE
(ROLL INT,
SNAME VARCHAR(50))
INSERT INTO @TBLVAR
SELECT ROLL, SNAME FROM my_STUDENT'
EXEC (@SQLSTRING);
END
END
GO
The Example works fine but the table variable is not accessible from outside the dynamic SQL. The reason is the dynamic SQL is not part of the procedure, but constitutes a scope of its own. So if we go to the simple solutions we must use the temporary table (#, ##) to solve this problem.
Or if we stick to the table variable we must go to the hybrid solutions for it.
IF OBJECT_ID('my_proc') IS NOT NULL
BEGIN
DROP PROCEDURE my_proc;
END
GO
CREATE PROCEDURE my_proc
(
@p_OpeningMood INT = 4
)
AS
DECLARE @SQLSTRING VARCHAR(2000)
DECLARE @TBLVAR TABLE
(ROLL INT,
SNAME VARCHAR(50))
BEGIN
IF @p_OpeningMood = 4
BEGIN
SET @SQLSTRING = 'SELECT ROLL, SNAME FROM my_STUDENT'
INSERT INTO @TBLVAR
EXEC (@SQLSTRING);
END
SELECT * FROM @TBLVAR;
END
GO
-- Executing the Stored Proc
EXEC my_proc
@p_OpeningMood=4
-- Output
ROLL SNAME
1 Joydeep
2 Sukamal
3 Sandip
4 Samgram jit
The above example works fine and we can get the desired result.
Related tropics
1. Table-Valued Parameters
2. Temporary table and Table variable
3. How to TABLE or TABLE variable to SP
4. Dynamic SQL With sp_executesql()
Hope you like it.
One my previous article "Can view takes input parameters" I got some response from various sources like "Linked In" etc.
Is Table-Valued UDF is Performance Killer
Here some of my readers give me some comments like
"A view might be more efficient than a table valued functions"
I searched different blogs and article related to it and find that there are a common misconnect is the Table value function is a poor performer.
So I decide to wire this article related to the performance factor of Table Valued function by collecting some notes from different sources.
How many type of Table valued function does MS SQL Server supports
There are 2 types of table valued function
1. Single-statement table valued user defined functions, also called in-line
2. Multi-statement table valued user defined functions
The simple example of both is mentioned bellow
Single Statement Table Valued function
CREATE FUNCTION dbo.fn_TableVal
(@p_sal DECIMAL(20,2))
RETURNS TABLE
AS RETURN
(
SELECT *
FROM emp_matsre
WHERE empSal>@p_sal
)
GO
Multi-statement table valued Function
CREATE FUNCTION dbo.fn_TableVal
(@p_sal DECIMAL(20,2))
RETURNS @Ret_Table TABLE
(idno INT,
empName VARCHAR(50),
empSal DECIMAL(20,2))
AS
BEGIN
INSERT INTO @Ret_Table
SELECT *
FROM emp_matsre
WHERE empSal>@p_sal
RETURN
END
GO
The single-statement table valued function sometimes called the views with parameters.
Execution of these UDF's will be incorporated directly into the execution plan with the rest of the associated query. Indexes may be used if the query within the UDF is well constructed. It is generally safe constructs to use within your queries.
Multi-statement UDF's are built upon the structure of table variables.
These are not incorporated within the rest of the execution plan and usually show up as a table scan against a table with one row. The problem is your query may have more than one row. When the multi-statement UDF's are used on large sets of data they cause very serious performance problems. They won't scale well, and the performance issues are masked by the apparent low cost of the operations involved. We need to be extremely careful when using the multi-statement table valued UDF's. The problem is that the system sees the BEGIN/END and runs the PROCEDURE in a different context. Without it, it takes the subquery in the return() call and can simplify it out.
Non-inline functions do not contribute towards the cost reported by an execution plan. You need to use Profiler to see their effect.
Hope you like it.
Table-Valued Parameters
In my previous article I am trying to demonstrate, "How to pass a table or table variable in a stored procedure via xml string".
Some of my reader, asking about Table-valued parameters, the new feature of SQL Server 2008.
In this article I am trying to demonstrate related to Table-Valued parameters in SQL server 2008.
Hope it will be helpful for all of my readers.
The table-valued parameters are the new feature of SQL Server 2008. By this we can send the multiple rows of data in a stored procedure or a function without creating any temporary table. Before SQL server 2008 it is not possible to send the table or table variable in the stored procedure or function.
For the Table-value parameters we must follow the following tasks mentioned bellow.
1. Create the table structure
2. Create stored procedure that has Table type parameter.
3. Create the table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. Passes the variable to the procedure.
The Table Definition
CREATE TABLE emp
(
empID INT NOT NULL PRIMARY KEY,
empName VARCHAR(30) NULL
)
GO
Step-1 [ Create the table structure ]
CREATE TYPE empType AS TABLE
(
empID INT,
empName VARCHAR(30)
);
GO
Step-2 [ Create stored procedure that has Table type parameter ]
CREATE PROCEDURE my_Employee
@Iemp empType READONLY
AS
INSERT INTO emp(empID,empName)
SELECT * FROM @Iemp;
GO
Please note the table-value parameter must be passes as readonly parameters. We can only perform DML operation in the body of the routine.
Step-3 [ Create the table type variable and reference the table type ]
DECLARE @Iemp AS empType;
Step-4 [ Using the INSERT statement and occupy the variable ]
INSERT INTO @Iemp(empID, empName)
VALUES (1,'Joydeep Das'),
(2,'Sudip Das'),
(3,'Tuhin Shina'),
(4,'Palash Paul')
Step-5 [ Passes the variable to the procedure ]
EXEC my_Employee @Iemp;
GO
SELECT * FROM emp
The complete code set to execute
CREATE TABLE emp
(
empID INT NOT NULL PRIMARY KEY,
empName VARCHAR(30) NULL
)
GO
CREATE TYPE empType AS TABLE
(
empID INT,
empName VARCHAR(30)
)
GO
CREATE PROCEDURE my_Employee
@Iemp empType READONLY
AS
INSERT INTO emp(empID,empName)
SELECT * FROM @Iemp;
GO
BEGIN
DECLARE @Iemp AS empType;
INSERT INTO @Iemp(empID, empName)
VALUES (1,'Joydeep Das'),
(2,'Sudip Das'),
(3,'Tuhin Shina'),
(4,'Palash Paul')
EXEC my_Employee @Iemp
END
GO
SELECT * FROM emp
Hope you like it.
