Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

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

Dec 17 2012 12:00AM by Nakul Vachhrajani   

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.

Tags: #SQLServer, SQL Server, Administration, DBA, T-SQL, #TSQL


Nakul Vachhrajani
4 · 36% · 11645
6
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • 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
    
    commented on Dec 17 2012 9:51AM
    Marc Jellinek
    95 · 2% · 586
  • @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.

    commented on Dec 17 2012 11:41AM
    Nakul Vachhrajani
    4 · 36% · 11645

Your Comment


Sign Up or Login to post a comment.

"#0222 - SQL Server - Stored procedures and temporary tables - Scope, Modification and some fun" rated 5 out of 5 by 6 readers
#0222 - SQL Server - Stored procedures and temporary tables - Scope, Modification and some fun , 5.0 out of 5 based on 6 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]