How do I Query the Result Set of a Query in MS SQL 2000

I'd like some advice on how to create a query.

In my stored procedure, I've got several variables which are used to form a query.  The query is created as a string and then executed:

CREATE PROCEDURE [dbo].[prcGetUCC]
-- Declare input paramters
@SortExp VARCHAR(100) = NULL,
@Branch VARCHAR(20) = NULL,
@BranchJ VARCHAR(20) = NULL

IF @Branch ='All'
    BEGIN
    SET @SQLStatement='SELECT * FROM vUCC ORDER BY ' + @SortExp + ' ASC'
    END
ELSE
    BEGIN
    SET @SQLStatement='SELECT * FROM vUCC WHERE BoO = ''' + @BranchJ + ''' OR
    BoO = ''' + @Branch1 + ''' OR BoO = ''' + @Branch9 + ''' ORDER BY ' + @SortExp + ' ASC'
    END
EXEC (@SQLStatement)

What I want to do is then query the results of this query and further filter the data.  Something like this:

SELECT * FROM (The Result Set of the Previous Query) WHERE ColumnA = ValueB

Is this at all possible?
LVL 15
Joseph HornseyPresident and JanitorAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, but you have to include it in the dynamic sql altogether:

CREATE PROCEDURE [dbo].[prcGetUCC]
-- Declare input paramters
@SortExp VARCHAR(100) = NULL,
@Branch VARCHAR(20) = NULL,
@BranchJ VARCHAR(20) = NULL 
AS
SET NOCOUNT ON
IF @Branch ='All'
    BEGIN
    SET @SQLStatement='SELECT * FROM vUCC ORDER BY ' + @SortExp + ' ASC'
    END
ELSE
    BEGIN
    SET @SQLStatement='SELECT * FROM vUCC WHERE BoO = ''' + @BranchJ + ''' OR
    BoO = ''' + @Branch1 + ''' OR BoO = ''' + @Branch9 + ''' ORDER BY ' + @SortExp + ' ASC'
    END 
SET @sqlstatement = 'SELECT * FROM (' + @sqlstatement + ') l WHERE ColumnA = ValueB ' 
EXEC (@SQLStatement)

Open in new window

0
 
Joseph HornseyPresident and JanitorAuthor Commented:
No way.  Really?

So, you can really SELECT Value FROM (SELECT DiffValue FROM DiffTable) WHERE This = That?

I'll try this in the morning and will post back.  Unfortunately, I'm beat.

Thanks!

<-=+=->
0
 
imitchieConnect With a Mentor Commented:
SELECT * FROM (The Result Set of the Previous Query) ALIAS WHERE ColumnA = ValueB

As long as you give it an alias. Angel's sample names it as L (lowercase). It can be used just like a normal "table", so you can even reference fields in it.
Take care that if you alias the columns, then you reference by alias, not original name. i.e.

SELECT * FROM ( SELECT Cost * 1.4 as SalePrice, ProductName FROM PRODUCTS) ProdAndSalePrice WHERE SalePrice > 100
0
 
Joseph HornseyPresident and JanitorAuthor Commented:
I've split the points because I didn't notice the lower-case L in angel's example.  If imitchie hadn't clarified that, I would have totally missed it.

Thank you both so much for your help!

By the way, is this an example of a "derived table"?

<-=+=->
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.