Fun with MS SQL
This post contains a few TSQL tidbits, mainly for my future reference…
Bits and Pieces
A quick way of flipping the value of a bit field is to use the bitwise (^) operator, for example:
UPDATE mytable SET mybitcol = mybitcol ^ 1
What this does is…
(0 ^ 1) 0000 0001 ---- 0001 (1 ^ 1) 0001 0001 ---- 0000
Nothing is Permanent
So you’ve heard of temporary tables in TSQL, but have you heard of temporary stored procedures? Just like with temp tables we simply prefix the name of the procedure with the hash (#) symbol.
CREATE PROCEDURE #IamTemporary AS BEGIN PRINT 'All good things must come to an end' END
And when the session ends so does the life of the temp procedure. Want to make the procedure available outside the current session? Use double hashes (##)!
Finding this procedure is a little more tricky than usual. Let’s say you wanted to check if the procedure exists and drop it if it does. Normally you would simply query against sys.objects within your database, with temp procedures you need to query within tempdb, kinda makes sense huh but you what you might not be aware of is that the name is not what you think.
SELECT name FROM tempdb.sys.objects WHERE type = 'P' AND name LIKE '#IamTemporary%' -- returns name #IamTemporary____________________________________________________________________________________________________________00009464
See that? The name is suffixed for some reason. What could that reason be? Well if we open a new tab in SSMS and run the create procedure script again. Then run the above query to search tempdb.sys.objects we find that we get 2 records returned. Each has a slightly different suffix, which appears to be tied to the session.
So instead, to clean up the procedure for the current session we could do something like:
IF OBJECT_ID('tempdb.dbo.#IamTemporary') IS NOT NULL BEGIN PRINT 'DROPPING' DROP PROCEDURE #IamTemporary END GO