Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Jan 18, 2005

Manipulate Stored Procedure Resultsets as If They Were Simple Queries

You already know that stored procedures offer enormous benefits over direct SELECT statements. But one of the things you tend to have to sacrifice when you replace a simple SELECT with a stored procedure is direct control over the resultset.

For example, you can't simply throw an ORDER BY clause into the mix to change the display order of the rows. But thanks to the OPENROWSET statement, you can take back this control.

Consider the stored procedure Ten Most Expensive Products in the Northwind database. To execute it, as you know, all you need to do is:


EXEC Northwind..[Ten Most Expensive Products]
But what if you want to manipulate the results, say, with an ORDER BY or a WHERE clause? The usual solution would be to pump the resultset into a temporary table, manipulate that temp table, then dispose of it. But with OPENROWSET, you can dispense with this step. Here are a few examples which should get you started:

SELECT TMEP.*
FROM OPENROWSET('SQLOLEDB', '<server>';'<user id>';'<password>',
	'EXEC Northwind..[Ten Most Expensive Products]') AS TMEP
WHERE UnitPrice BETWEEN 50 AND 75 -- Narrow down the resultset

SELECT TMEP.*
FROM OPENROWSET('SQLOLEDB', '<server>';'<user id>';'<password>',
	'EXEC Northwind..[Ten Most Expensive Products]') AS TMEP
ORDER BY TenMostExpensiveProducts -- Alter the stored
procedure's sort order

SELECT TMEP.TenMostExpensiveProducts -- Return only the name; we
don't care about the UnitPrice
FROM OPENROWSET('SQLOLEDB', '<server>';'<user id>';'<password>',
	'EXEC Northwind..[Ten Most Expensive Products]') AS TMEP

SELECT TOP 5 TMEP.* -- The procedure returns a top 10, but we
actually only want the top 5 this time
FROM OPENROWSET('SQLOLEDB', '(local)';'sa';'bingo',
	'EXEC Northwind..[Ten Most Expensive Products]') AS TMEP
Obviously, you'll need to substitute the valid and applicable values for <server>, <user id>, and <password>.
Parthasarathy Mandayam
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap