Temporary Stored Procedures on 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.
- SQL Case Statement - October 15, 2008
- Getting ColdFusion SQL Statements from SQL Server Trace - June 16, 2008
- Try Catch for SQLServer T-SQL - April 7, 2008
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008
- Backwards LIKE Statements - January 10, 2007
@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).
- ColdFusion returning empty response with server-error: true
- Careful applying CF11u16, CF2016u8, CF2018u2
- Sessions don't work in Chrome but do in IE
- csrfVerifyToken does not invalidate the token
- The cf_sql_ is optional in cfqueryparam
- Cookie Expires / Max-Age 1969-12-31T23:59:59.000Z
- Burst Throttling on AWS API Gateway Explained
- How to Resolve Java HTTPS Exceptions