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.
ü 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.
ü 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.
ü 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.
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.
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.
Posted by: MR. JOYDEEP DAS
