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 #t1 (digit INT, name NVARCHAR(10));
GO
CREATE PROCEDURE #insert_to_t1
(
@digit INT
, @name NVARCHAR(10)
)
AS
BEGIN
merge #t1 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_t1 1,'One';
EXEC #insert_to_t1 2,'Two';
EXEC #insert_to_t1 3,'Three';
EXEC #insert_to_t1 4,'Not Four';
EXEC #insert_to_t1 4,'Four'; --update previous record!
SELECT * FROM #t1;
|
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 ##t1 (digit INT, name NVARCHAR(10));
GO
CREATE PROCEDURE ##insert_to_t1
(
@digit INT
, @name NVARCHAR(10)
)
AS
BEGIN
merge ##t1 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_t1 1,'One';
EXEC ##insert_to_t1 2,'Two';
EXEC ##insert_to_t1 3,'Three';
EXEC ##insert_to_t1 4,'Not Four';
EXEC ##insert_to_t1 4,'Four'; --update previous record!
SELECT * FROM #t1; --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!