Temporary Stored Procedures on SQL Server

I posted about doing fast bulk inserts with PostgreSQL last week, and with MySQL a while back. Now its time for Microsoft SQL Server.

I'm using a technique similar to what I used for PostgreSQL for SQL server. I'm just creating a temporary stored procedure, and then calling it lots of times. I know you could also create a stored procedure on the server to do this, but here's how you might to it with a temporary one:

SET NOCOUNT ON
GO
CREATE PROC #tempInsertProc
  @id integer
AS
  INSERT INTO foo (bar) VALUES (@id)
GO
EXEC #tempInsertProc 10
GO
EXEC #tempInsertProc 11
GO
EXEC #tempInsertProc 12
GO
DROP PROC #tempInsertProc
GO
SET NOCOUNT OFF
GO

Temporary Stored Procedures

Temporary stored procedures on Microsoft SQL Server are prefixed with a pound sign #. One pound sign means that its temporary within the session, two pound signs ## means its a global temporary procedure, which can be called by any connection to the SQL server during its lifetime.

Check out Microsoft's documentation of CREATE PROCEDURE for more info.

Why temporary procedures?

Your probably wondering why create temporary procedures, when you can just create a permanent stored procedure? In most cases its probably better to use a permanent SP, but if your like me, and don't like putting too much logic in the DB, but need to use a stored procedure, then these are one way to go.