I found out about a tiny feature in SQL Server today that I never knew about and Mladen Prajdic persuaded me to blog about it. So here it is!

I suspect that most people reading this know that its possible to create temporary tables in SQL Server, right? It usually goes something like this:

SELECT [name]

INTO #tableName

FROM sys.tables

Easy enough! Well I found out today that you can create temporary procedures too. Check this out,

you can copy and paste the following into SSMS for a demo:





CREATE TABLE #t 1 ( digit INT , name NVARCHAR ( 10 ));

GO



CREATE PROCEDURE #insert_to_t 1

(

@digit INT

, @name NVARCHAR ( 10 )

)

AS

BEGIN

merge #t 1 AS tgt

using ( SELECT @digit , @name ) AS src ( digit , name )

ON ( tgt.digit = src.digit )

WHEN matched THEN

UPDATE SET name = src.name

WHEN NOT matched THEN

INSERT ( digit , name ) VALUES ( src.digit , src.name );

END ;

GO



EXEC #insert_to_t 1 1 , 'One' ;

EXEC #insert_to_t 1 2 , 'Two' ;

EXEC #insert_to_t 1 3 , 'Three' ;

EXEC #insert_to_t 1 4 , 'Not Four' ;

EXEC #insert_to_t 1 4 , 'Four' ; --update previous record!



SELECT * FROM #t 1 ;

What we're doing here is creating a procedure that lives for the life of the connection and which is then later used to insert some data into a table.

I wondered whether it was possible to make procedures globally temporary and sure enough it is (note the double hashes ##):

CREATE TABLE ##t 1 ( digit INT , name NVARCHAR ( 10 ));

GO



CREATE PROCEDURE ##insert_to_t 1

(

@digit INT

, @name NVARCHAR ( 10 )

)

AS

BEGIN

merge ##t 1 AS tgt

using ( SELECT @digit , @name ) AS src ( digit , name )

ON ( tgt.digit = src.digit )

WHEN matched THEN

UPDATE SET name = src.name

WHEN NOT matched THEN

INSERT ( digit , name ) VALUES ( src.digit , src.name );

END ;

GO



--execute this next bit in a different window (i.e. a different connection)

EXEC ##insert_to_t 1 1 , 'One' ;

EXEC ##insert_to_t 1 2 , 'Two' ;

EXEC ##insert_to_t 1 3 , 'Three' ;

EXEC ##insert_to_t 1 4 , 'Not Four' ;

EXEC ##insert_to_t 1 4 , 'Four' ; --update previous record!



SELECT * FROM #t 1 ; --this returned the expected 4 rows by the way!!!

So, what might we use this for? Well I can imagine that when doing a deployment that involves deploying data it

may be useful to only have a procedure live for the lifetime of the deployment - this little feature would work perfectly for that.

Did anyone else not know about this or was it just little old me? Can you think of any other uses?

@Jamiet



N.B. Thanks to Aaron Bertrand for his instructions here on producing readable code for blogs! I’m putting this comment here so I know where to find it next time!