Temporary Stored Procedures on SQL Server

September 07, 2005

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:

CREATE PROC #tempInsertProc
  @id integer
  INSERT INTO foo (bar) VALUES (@id)
EXEC #tempInsertProc 10
EXEC #tempInsertProc 11
EXEC #tempInsertProc 12
DROP PROC #tempInsertProc

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.

Related Entries

7 people found this page useful, what do you think?


Another thought. What if you create a temp table with no indexes in memory, insert everything in there, and then do an INSERT..SELECT statment to move the records to the real table?
That's a good idea, I'll have to test it out!
Can you explain a little more why you would actually want to do this? Have you found a performance improvement? I would think that heavy use of this would bloat up your tempdb. So would using temp tables. I used to use a lot of temp tables, but I've since switched to using table variables. They work about the same and you don't have to worry about deadlocking your tempdb. If you REALLY have a lot of records to import, you might want to try spooling them to text and using bulk insert.
Bulk inserts can also be very fast using prepared statements with sql server. Is it possible to put your statements from above within a <cfquery> to execute within ColdFusion? When I try it, I get the error message "Invalid token '@' found"
well maybe you dont have permission to create permanent stored procedures, this is a good way to test it until its ready for production then you can get your dba guy to do it
When I am creating the sql drive to use my database, in the step "change the default database to" the checkbox near "create temporary stored...." is always checked and hibernating!!!. my question is how can I deactivate the hibernation because I don't want to use a temporary file. thank you in advance.
something that not everyone is aware of, in addition to #temp tables, you can also use @temp tables.

@temp tables are more of a variable (smaller accessibility scope), and... LEAVE NO TRACES in the transaction logs.

I often use @temp tables, I rarely if ever use #temp tables (though you can index a #temp table whereas you can't a @temp table).

I'd also be mildly curious if something similar is possible with @temp sprocs (doubt it).

Recent Entries