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:

• Temporary table and dynamic sql
• Scope of temporary tables across procedures

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

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

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.