05 December 2011

TSQL development

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.

SP_Who_thumb.pngLet’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:

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:

The Words column is set to 11 characters in length because we inserted ‘Information’ which is 11 characters long:
TempTableProperties_thumb.png

sp_who02_thumb.pngWe 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. ConvertTableToText_thumb.pngNext 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.

Choose ‘commas’.

Commas_thumb.png

Showcharacters_thumb.pngNow make sure that paragraph marks are shown and you should then be left with something like this:

Paragraph_thumb.png

Let’s get to work making some edits and with Find/Replace.

  1. Take out the first line and type in “CREATE TABLE TempTable (
  2. 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
  3. 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.
  4. Place a closing ‘)’ at the end of the text, copy it all and paste it into an SSMS query.
  5. 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:

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():

and then alter the INSERT script slightly:

and you are logging the data along with when it was collected.

FriendsOfRedGate_2011_OnWhite_RGB_300px.

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 2720 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Jonathan Allen has been a SQL Server DBA since 1999, most enjoying performance tuning and development but also working with SSIS, SSRS to provide suitable business solutions. He is SQLSouthWest PASS Chapter Leader, blogs for Simple Talk, is a forum moderator at ask.sqlservercentral.com and is on Twitter. If you would like to find your nearest user group or just want to say hello then he would love to get an email from you.

View all articles by Jonathan Allen