#0222 – SQL Server – Stored procedures and temporary tables – Scope, Modification and some fun


Recently, I was asked a question on the ASK platform on this site (http://beyondrelational.com/modules/3/ask/questions/18239/table-variable-as-output-parameter-in-sql-server-stored-procedure-not-as-readonly-parameter.aspx):

How can one pass a table (as a variable or otherwise) to a stored procedure, but not as a read-only parameter?

As you already know, table valued parameters can be passed to stored procedures only as READONLY parameters. So, how can one implement a scenario wherein a table needs to be passed over to a procedure, modify the table inside it and then consume the results outside of the procedure?

The answer is available in the following posts from Madhivanan:

To summarize the solution:

  1. Create a temporary table
  2. Within the same session, execute a stored procedure – the temporary table will be available within this procedure
  3. Modify the contents of the table as required
  4. Once the procedure completes, the table continues to be available as long as the session is not changed

With this method of sharing tables between stored procedures, here’s an interesting test. In this test, we are creating another temporary table with the same name within the stored procedure and attempting to manipulate it.

USE tempdb
GO
--00. Safety check
IF OBJECT_ID('usrproc_SharingTempTablesTesting') IS NOT NULL
BEGIN
DROP PROCEDURE usrproc_SharingTempTablesTesting;
END
GO

IF OBJECT_ID('#SharingTempTables') IS NOT NULL
BEGIN
DROP TABLE #SharingTempTables;
END
GO

--01. Create temp. table
CREATE TABLE #SharingTempTables (iA INT DEFAULT 0,
iB INT DEFAULT 0,
iT AS (iA + iB)
)
GO

--02. Create stored procedure
CREATE PROCEDURE dbo.usrproc_SharingTempTablesTesting
@intA INT,
@intB INT
AS
BEGIN
SET NOCOUNT ON

--ATTENTION:
--Creating a temp. table with the same name inside of the procedure
CREATE TABLE #SharingTempTables (iA INT DEFAULT 0,
iB INT DEFAULT 0,
iT AS (iA - iB)
)

INSERT INTO #SharingTempTables (iA, iB) VALUES (@intA, @intB)

INSERT INTO #SharingTempTables (iA, iB)
SELECT 10*@intA, 20 * @intB FROM #SharingTempTables

--Selecting data from the temp. table
SELECT'Inside',* FROM #SharingTempTables
END
GO

Let’s now insert some test data into the stored procedure and execute the stored procedure (the scripts have been split into two parts for sake of better understanding. The entire script needs to be executed within the same session):

--Continues from the script above...
USE tempdb
GO

--03. Insert some test data
INSERT INTO #SharingTempTables (iA, iB)
VALUES (1,1), (1,2), (1,3), (1,4)
GO

--04. Execute the stored procedure
EXEC dbo.usrproc_SharingTempTablesTesting @intA = 2, @intB = 1
GO

--05. Select data from the temp table
SELECT 'Outside',* FROM #SharingTempTables
GO

image

Something did not go as expected. Had we used permanent tables instead of temporary tables, we would surely have ended up in an error because we cannot have two tables with the same name within the same database and schema. But, we did not encounter any error.

What’s even more interesting is that the results returned from inside of the stored procedure indicate that the data supplied via the table outside of the procedure has not been used.

Summarizing the behaviour until now:

  1. No error is encountered if a temp. table with a name duplicate to another pre-existing temp. table is used
  2. In such cases where temp. tables with the same name exist, the inner temp. table operates on a data set different from the one supplied from outside of the procedure

So, what’s going on?

We will make a small change to the procedure and the calling script as shown below – we will try to fetch the OBJECT_ID() of the temporary table alongwith the result sets.

--06. Create stored procedure
ALTER PROCEDURE dbo.usrproc_SharingTempTablesTesting
    @intA INT,
    @intB INT
AS
BEGIN
  SET NOCOUNT ON
--ATTENTION:
--Creating a temp. table with the same name inside of the procedure
CREATE TABLE #SharingTempTables (iA INT DEFAULT 0,
iB INT DEFAULT 0,
iT AS (iA - iB)
)

INSERT INTO #SharingTempTables (iA, iB) VALUES (@intA, @intB)

INSERT INTO #SharingTempTables (iA, iB)
SELECT 10*@intA, 20 * @intB FROM #SharingTempTables

--Selecting data from the temp. table
SELECT'Inside', OBJECT_ID('#SharingTempTables'),* FROM #SharingTempTables
END
GO

--07. Execute the stored procedure
EXEC dbo.usrproc_SharingTempTablesTesting @intA = 2, @intB = 1
GO

--08. Select data from the temp table
SELECT 'Outside',OBJECT_ID('#SharingTempTables'),* FROM #SharingTempTables
GO

image

As can be seen from the results, the inner temp. table is actually a separate object, and not the same as the one used outside of the stored procedure.

This is because the temp. table with duplicate name defined inside of the procedure is limited to the procedure in scope. If a table with duplicate name was not defined, the table defined outside of the procedure would still be available inside of the procedure.

Do you have any other interesting observations regarding temp. tables? If so, do share them here – I am sure the entire community would be eager to hear about them!

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

3 thoughts on “#0222 – SQL Server – Stored procedures and temporary tables – Scope, Modification and some fun

  1. marc_jellinek@hotmail.com

    Temporary tables are an external dependency. They can be particularly troublesome when connection pooling is used. SSIS also has issues with them.

    Instead, use table-typed parameters to push data into your stored procs and use a separate result set as output from your stored proc:

    CREATE TYPE [dbo].[MyTable] AS TABLE
    (
    [id] [int],
    [value] [nvarchar](128)
    )
    GO

    CREATE PROCEDURE [dbo].[Capitalize]
    (
    @myTable [dbo].[MyTable] READONLY
    )
    AS
    BEGIN
    DECLARE @myOutput [dbo].[MyTable]

    INSERT INTO @myOutput([id], [value])
    SELECT [id], UPPER([value]) FROM @myTable

    SELECT [id], [value] FROM @myOutput
    END
    GO

    DECLARE @myTable [dbo].[MyTable]
    DECLARE @myOutput [dbo].[MyTable]

    INSERT INTO @myTable([id], [value])
    VALUES
    (1, ‘one’),
    (2, ‘two’),
    (3, ‘three’)

    INSERT INTO @myOutput([id], [value])
    EXEC [dbo].[Capitalize] @myTable

    SELECT * from @myOutput

    Returns:

    id value
    ———– ——————————————————————————————————————————–
    1 ONE
    2 TWO
    3 THREE

    Like

    Reply
  2. Nakul Vachhrajani

    @Marc: Absolutely agree with you. This approach is generally found on solutions coming over from the days of SQL 2000 and when teams have generally did the bare minimum to ensure that their databases work on newer releases of SQLServer (for example, a mindset like “as long as the DB can function with a compatibility level of 80 on a SQL Server 2008R2, all is well”).

    I have seen this create a problem with connection pooling. Today, I learnt that even SSIS faces issues with them – that’s an additional point for me to push the legacy teams towards [table valued parameters which can be passed to stored procedures only as READONLY parameters][1].

    [1]: http://bit.ly/Z60RSf

    Like

    Reply
  3. Pingback: #0376 – SQL Server – Msg 2714: There is already an object named ‘#tableName’ in the database. | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s