TSQL developmentPublished 5 December 2011 3:00 am
or: How I Learned to Stop Worrying and Love
the Bomb Word*
Microsoft Office Word is useful. There, I have confessed it, it’s useful to me while I am working with TSQL. It’s possibly only the search and replace function that I like but it’s wrapped in Word so I have to take it like that. I use it a lot to help me capture the results of a stored procedure.
Let’s say we want to review what processes are connected to our server so we execute the stored procedure sp_who. This is useful but maybe we want to store this information for consumption later, or for comparison with another result set. We cant join to a stored procedure so we need to get this data into a table and then we can join to it.
We can get the results of a TSQL query into a table by executing a query along the lines of:
SELECT 1 AS [Numbers] ,
'Information' AS [Words]
but this has drawbacks: if the table already exists then the code needs to take a different form and if the data collected first was not a good sample then you will get insert errors later on. Checking in TempDB for the details of the temp table created above we see that the columns are pretty restrictive:
SELECT [t].[name] ,
FROM [sys].[tables] AS t
INNER JOIN [sys].[columns] AS c ON [t].[object_id] = [c].[object_id]
INNER JOIN [sys].[types] AS t2 ON [c].[user_type_id] = [t2].[user_type_id]
WHERE [t].[name] LIKE '%temp%'
We wont know what data we will get from sp_who so creating a table on the fly is ruled out. We need to know what the data might look like and cater for that. Books OnLine is our friend here, and Word is about to justify it’s installation on your PC. Open Word and then locate the description of the stored procedure in Books OnLine and in particular the Result Sets section, next select the contents of the whole table, copy it and paste it into Word. Check the full information about the procedure here – http://msdn.microsoft.com/en-us/library/ms174313.aspx.
This will paste into Word as a table. Select the description column and delete it. Dont just hit the delete key, you need to delete the whole column. Next select the whole table and move to the Tables, Layout menu and select Convert to Text which will in turn ask you how to delimit the columns.
Let’s get to work making some edits and with Find/Replace.
- Take out the first line and type in “CREATE TABLE TempTable (”
- Press Ctrl and H and in the Find What box type a single comma. In the Replace With box type ” ” that is a single space character. Make the change by clicking Replace All
- Press Ctrl and H and in the Find What box type a “^p”. In the Replace With box type “, ^p” that is a comma followed by a space character and then ‘^p’. Make the change by clicking Replace All.
- Place a closing ‘)’ at the end of the text, copy it all and paste it into an SSMS query.
- You will need to remove an extra comma on line 1 and possibly 2 on the last lines.
Executing this script will create the table and then this sort of query will work:
INSERT INTO temptable EXEC [sys].[sp_who]
SELECT * FROM temptable
If you want to store results of a stored procedure over time then simply add a column to the table creation script that has a default value of GetDate():
CollectedOn DATETIME DEFAULT GETDATE()
and then alter the INSERT script slightly:
INSERT INTO[dbo].[TempTable] ([spid], [ecid], [status], [loginame], [hostname], [blk], [dbname], [cmd], [request_id] ) EXEC [sys].[sp_who]
and you are logging the data along with when it was collected.
I hope this tip saves you some time working with results from stored procedures, let me know how you get on and if you have any time saving techniques you’d like to share.
* – Blog title is a poor play on the words of a great film title – “Dr Strangelove or: How I Learned to Stop Worrying and Love the Bomb”.
** – Be careful that you run this on a test server where there isn’t going to be a table already called temp or you will see odd results. TempDB holds all temporary tables so could have lots of objects with the same name.