Using table valued results of stored procedures

June 20, 2008 at 6:31 PMAndre Loker

If a stored procedure returns a table of data, it's not as easy as one might think to work with the result. Assume the following scenario:

- we have a table Account

image

- we have a (admittedly silly) stored procedure that performs some query and returns "Account" rows:

   1: CREATE PROCEDURE sp_GetAccounts
   2: AS
   3:     SELECT * FROM Account;

- we want to perform additional filtering & projection on the data returned by the SP, like:

   1: SELECT UserName, Email 
   2: FROM (EXEC sp_GetAccounts)
   3: WHERE Email LIKE '%.de'
   4: ORDER BY UserName ASC;

This, however, does not work:

Incorrect syntax near the keyword 'EXEC'.

It seems that we cannot use the result of the SP as a normal table. But here's a little trick that will do what we want:

   1: -- create a table variable (ie. a temporary in-memory table)
   2: DECLARE @results TABLE (
   3:  ID int,
   4:  UserName nvarchar(50),
   5:  Email nvarchar(256),
   6:  HashedPasswod nvarchar(32),
   7:  Salt nvarchar(16)
   8: );
   9:  
  10: -- fill it with the results from the SP
  11: INSERT INTO @results EXEC sp_GetAccounts;
  12:  
  13: -- perform normal queries on the table
  14: SELECT UserName, Email 
  15: FROM @results
  16: WHERE Email LIKE '%.de'
  17: ORDER BY UserName ASC;

We simply create a table variable compatible to the Account table to temporarily hold the results of the stored procedure. This table is filled using the SP (INSERTs work with SPs). We can then execute queries against that table variable as we like. By using a table variable (instead of a temporary table) everything is held in memory and we don't need to drop the table explicitly after we're done using it.

I'm not a SP guru, so if there are simpler ways to achieve the same result, drop me a comment.

Posted in: Databases | Snippets

Tags: , ,

Firefox 2 and 3 side by side

June 19, 2008 at 11:08 AMAndre Loker

So, Firefox 3 is out. The curious part in me wants to upgrade, the reasonable part wants to stay with version 2 ("will my add-ins work with v3, yadda yadda"). Even worse, the developer in me wants to install both versions, to see how my projects work on either one. Side by side installation is not really intended by Mozilla. But luckily it's still possible.

This article will show you how you can run both FF 2 and FF 3 on the same machine. We will not change the FF 2 installation. We'll only add a non-installation version of Firefox 3 that can be launched on demand without changing the FF 2 profile. The article is based on this article and this article (German).

Before we start: I am not responsible for any trouble that this guide causes on your computer. It works fine on my machine, but don't blame me if your Firefox profile get messed up, your keyboard catches fire or your girlfriend leaves you etc.

  1. Make a backup of your current Firefox installation. I mean seriously, make a backup. I recommend MozBackup, it's easy yet powerful and gets its job done.
  2. Create a new Firefox profile for our future Firefox 3. We don't want FF 3 and FF 2 to use the same profile, so this step is important.
    1. Launch the profile manager with: firefox -profilemanager -no-remote
    2. Select "Create Profile..." to create a new profile. You can name it whatever you like (here I named it ff3test), just remember the name for later!
      image
  3. Download Firefox 3 from mozilla.com. You should have 'Firefox Setup 3.0.exe' by now. Do NOT install it!
  4. Extract the content of 'Firefox Setup 3.0.exe' to a folder on your drive. I use 7-zip for this, but other tools may work as well. With 7-zip, right click on 'Firefox Setup 3.0.exe' and select 7-zip -> Extract to "Firefox Setup 3.0\". The folder with the extracted content should look like this:
    image
  5. Remove unnecessary files: remove the following files/folders:
    1. optional
    2. removed-files.log
    3. setup.exe
  6. Move the content of localized to nonlocalized. Confirm to overwrite all files when asked. You can delete the "localized" folder safely.
  7. The "nonlocalized" folder now contains all required FF 3 binaries. You may rename it (to let's say "Mozilla Firefox 3") and move it (to "Program Files (x86)") if you like.
  8. Create a shortcut to FF 3. In this example I create a shortcut on my desktop.
    1. Locate firefox.exe in the (former) "nonlocalized" folder, right click on it, choose 'Send to' -> 'Desktop'
      image
    2. Locate the shortcut on your desktop, rename it if you like, right click it and choose 'Properties'
    3. Append "-P ff3_profile -no-remote" to the "Target" text field, where ff3_profile is the name of the designated Firefox 3 profile you created in step 2. In my case it was called "ff3test", so my text field looks like:
      image

That's it! You can now continue to use FF2 as usual. If you feel like FF3'ing, just use the shortcut we created to run the shiny new Firefox 3. Of course, there is no uninstall option for FF 3 (we did not install it in the first place). To get rid of FF3 just delete the respective folder, the shurtcut and if necessary the profile.

image

NB: the first time you start FF2 after this procedure the profile manager may appear. Just select the default profile and check "Dont's ask at startup".

Posted in: Other

Tags:

Older versions of Firefox

June 19, 2008 at 10:32 AMAndre Loker

Need an older release of Firefox? Here you go: http://releases.mozilla.org/pub/mozilla.org/firefox/releases/

Posted in: Other

Tags:

Speed up Firefox when using Development Server

June 18, 2008 at 1:17 PMAndre Loker

The problem

Using Firefox on Vista to view websites hosted by the Visual Studio Development Server can be unbearably slow. Pages take aeons (i.e. several seconds) to load while viewing the same site under IE does not suffer from this issue.

The solution

As mentioned in this blog post, this problem can be solved like this:

  1. Open the Firefox config (browse to about:config)
  2. Locate the setting: network.dns.disableIPv6 (type "v6" into the filter field)
  3. Change the value to true (double click the row)

The result

Now sites on the built in development server work fast even under Firefox and Vista.

The conclusion

What can I say - it works :-)

Update 06/19/2008: works with Firefox 3 as well

Posted in: ASP.NET

Tags: ,

I lost my first hard drive

June 16, 2008 at 2:25 PMAndre Loker

Today for the first time I lost a hard drive. I mean literally: I lost it. Several weeks ago I removed it from a computer and I can't remember where I put it. Gotta spend my time searching for it now instead of doing something productive.

Gosh, it's Monday again :-(

Update: I found it. It was already plugged into another computer. I can't remember to have put it there, though...

Posted in: Off topic

Tags: