Multiple Select Statements

Multiple Select Statements

Post by dseller » Wed, 03 Mar 2004 00:46:11



I am trying to issue two SELECT statements where the first one not only returns a result set but also sets a variable for use in the next SELECT statement. The two SELECT statements are listed below. I would like to have the the first statement return the result and somehow pass the pageid on to the next query. Any thoughts?
<code>
CREATE PROCEDURE GetCurrentPage

)
AS

SELECT TOP 1 Page.PageId, Page.CompanyId, Page.CompanyName, Page.BusinessGoals, Page.ShortDescription, Page.LongDescription, Page.DatePosted
FROM Page

ORDER BY Page.DatePosted DESC;



SELECT PageProduct.PageProductId, PageProduct.PageId, PageProduct.ProductId, PageProduct.Name, Product.Brand, PageProduct.Model, PageProduct.LongDescription, PageProduct.Image, PageProduct.Url
FROM PageProduct

GO
</code>

 
 
 

Multiple Select Statements

Post by Louis Davidso » Wed, 03 Mar 2004 00:51:51


If pageId is the key, then just do something like this:



FROM Page

ORDER BY Page.DatePosted DESC;

Quote:> SELECT TOP 1 Page.PageId, Page.CompanyId, Page.CompanyName,

Page.BusinessGoals, Page.ShortDescription, Page.LongDescription,
Page.DatePosted

> FROM Page


<Other select>

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)


Quote:> I am trying to issue two SELECT statements where the first one not only

returns a result set but also sets a variable for use in the next SELECT
statement. The two SELECT statements are listed below. I would like to have
the the first statement return the result and somehow pass the pageid on to
the next query. Any thoughts?
> <code>
> CREATE PROCEDURE GetCurrentPage
> (

> )
> AS

> SELECT TOP 1 Page.PageId, Page.CompanyId, Page.CompanyName,

Page.BusinessGoals, Page.ShortDescription, Page.LongDescription,
Page.DatePosted
> FROM Page

> ORDER BY Page.DatePosted DESC;



> SELECT PageProduct.PageProductId, PageProduct.PageId,

PageProduct.ProductId, PageProduct.Name, Product.Brand, PageProduct.Model,
PageProduct.LongDescription, PageProduct.Image, PageProduct.Url
> FROM PageProduct

> GO
> </code>


 
 
 

Multiple Select Statements

Post by Delbert Glas » Wed, 03 Mar 2004 06:19:35


BTW, this:
Quote:> ...not only returns a result set but also sets a variable ...

is not allowed as noted in BOL:

Quote:>Note A SELECT statement that contains a variable assignment
>cannot also
>be used to perform normal result set retrieval operations.

Bye,
Delbert Glass


Quote:> I am trying to issue two SELECT statements where the first one not only

returns a result set but also sets a variable for use in the next SELECT
statement. The two SELECT statements are listed below. I would like to have
the the first statement return the result and somehow pass the pageid on to
the next query. Any thoughts?
> <code>
> CREATE PROCEDURE GetCurrentPage
> (

> )
> AS

> SELECT TOP 1 Page.PageId, Page.CompanyId, Page.CompanyName,

Page.BusinessGoals, Page.ShortDescription, Page.LongDescription,
Page.DatePosted
> FROM Page

> ORDER BY Page.DatePosted DESC;



> SELECT PageProduct.PageProductId, PageProduct.PageId,

PageProduct.ProductId, PageProduct.Name, Product.Brand, PageProduct.Model,
PageProduct.LongDescription, PageProduct.Image, PageProduct.Url
> FROM PageProduct

> GO
> </code>