Using query results within a stored procedure

Using query results within a stored procedure

Post by Chuck Jungman » Wed, 13 Feb 2002 07:13:59



I am trying to create a stored procedure that runs a query on a large
database of points in an area.  In addition to the recordset of
points, I would like to return, in output parameters,  the average
latitude and longitude of the set of returned points in order to
generate a map that is centered around all the points.

This is a pseudo-code example of the query:

CREATE PROCEDURE GetPoints






SELECT Lat, Long, Picture
FROM PointDB


---- After running the query, I would like to query the result set
 SELECT AVG(Lat), AVG(Long)
    FROM [above recordset]

Is this possible?  A temporary table would work, but it seems
redundant.

Thanks,
Chuck

 
 
 

Using query results within a stored procedure

Post by Narayana Vyas Kondredd » Wed, 13 Feb 2002 07:23:22


Chuck, as you said, a temporary table is one of the alternatives. Other than
that, you could use table variables in this case. See Books Online for more
information. Don't have SQL2K handy to post an example.
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


I am trying to create a stored procedure that runs a query on a large
database of points in an area.  In addition to the recordset of
points, I would like to return, in output parameters,  the average
latitude and longitude of the set of returned points in order to
generate a map that is centered around all the points.

This is a pseudo-code example of the query:

CREATE PROCEDURE GetPoints






SELECT Lat, Long, Picture
FROM PointDB


---- After running the query, I would like to query the result set
 SELECT AVG(Lat), AVG(Long)
    FROM [above recordset]

Is this possible?  A temporary table would work, but it seems
redundant.

Thanks,
Chuck

 
 
 

Using query results within a stored procedure

Post by oj » Wed, 13 Feb 2002 07:24:18


chuck,

just run another query to set the outputs...

CREATE PROCEDURE GetPoints






as
set nocount on
SELECT Lat, Long, Picture
FROM PointDB


---- After running the query, I would like to query the result set

FROM PointDB


--
-oj
http://rac4sql.home.attbi.com


> I am trying to create a stored procedure that runs a query on a large
> database of points in an area.  In addition to the recordset of
> points, I would like to return, in output parameters,  the average
> latitude and longitude of the set of returned points in order to
> generate a map that is centered around all the points.

> This is a pseudo-code example of the query:

> CREATE PROCEDURE GetPoints






> SELECT Lat, Long, Picture
> FROM PointDB


> ---- After running the query, I would like to query the result set
>  SELECT AVG(Lat), AVG(Long)
>     FROM [above recordset]

> Is this possible?  A temporary table would work, but it seems
> redundant.

> Thanks,
> Chuck

 
 
 

Using query results within a stored procedure

Post by Hirantha Hettiarachch » Wed, 13 Feb 2002 07:33:14


Chuck,

SELECT AVG(a.Lat), AVG(a.Long)
    FROM(
SELECT Lat, Long, Picture
FROM PointDB


--
hth
Hirantha S. Hettiarachchi MCSD,MCDBA,OCP

Please reply only to the newsgroups.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Using query results within a stored procedure

Post by Narayana Vyas Kondredd » Wed, 13 Feb 2002 07:38:30


This wouldn't work if Chuck wanted to return both the original resultset and
the one with averages to the front-end :)

--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Chuck,

SELECT AVG(a.Lat), AVG(a.Long)
    FROM(
SELECT Lat, Long, Picture
FROM PointDB


--
hth
Hirantha S. Hettiarachchi MCSD,MCDBA,OCP

Please reply only to the newsgroups.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Using query results within a stored procedure

Post by oj » Wed, 13 Feb 2002 07:39:00


why selecting 3 cols and using derived table when you can just do it
directly? :)

--
-oj
http://rac4sql.home.attbi.com


> Chuck,

> SELECT AVG(a.Lat), AVG(a.Long)
>     FROM(
> SELECT Lat, Long, Picture
> FROM PointDB


> --
> hth
> Hirantha S. Hettiarachchi MCSD,MCDBA,OCP

> Please reply only to the newsgroups.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Using query results within a stored procedure

Post by Chuck Jungman » Wed, 13 Feb 2002 08:21:12


(By the way, thanks for all the quick responses)

This idea of running two queries occured to me: it's clearer to read.
However, it's an expensive query, requiring a full table scan.  I
didn't want to do it twice.  I wondered if SQL Server would optimize
this query internally to avoid running the query twice?



>chuck,

>just run another query to set the outputs...

>CREATE PROCEDURE GetPoints






>as
>set nocount on
>SELECT Lat, Long, Picture
>FROM PointDB


>---- After running the query, I would like to query the result set

>FROM PointDB



 
 
 

Using query results within a stored procedure

Post by oj » Wed, 13 Feb 2002 08:31:32


not based on what you've posted...btw, you should put index on lat and long
which should help with perf.

--
-oj
http://rac4sql.home.attbi.com


> (By the way, thanks for all the quick responses)

> This idea of running two queries occured to me: it's clearer to read.
> However, it's an expensive query, requiring a full table scan.  I
> didn't want to do it twice.  I wondered if SQL Server would optimize
> this query internally to avoid running the query twice?



> >chuck,

> >just run another query to set the outputs...

> >CREATE PROCEDURE GetPoints






> >as
> >set nocount on
> >SELECT Lat, Long, Picture
> >FROM PointDB


> >---- After running the query, I would like to query the result set

> >FROM PointDB



 
 
 

1. Using Query Results within Stored Procedure

I'd like to perform a query within a stored procedure and then use 3 or 4 of the fields from the returned recordset (consisting of only one row) from that query in an action statement within the same sp. It's trivial to get a single value (e.g. COUNT) but I need to use several of the fields.



Thanks.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for web developers.

2. Medical Diagnosis and Procedure codes needed

3. Retrieving result from a stored procedure called within another stored procedure

4. Staying with one record, Pdox7 forms

5. How do I retrieve the results of a dynamic query within a store procedure

6. Using SUM in SQL UPDATE command

7. Reusing Query Result Rowsets within a Stored Procedure

8. broken files (paradox)

9. Using stored procedures within stored procedures

10. using SQL stored procedure results in stored procedure

11. Using Stored Procedure's results in a Stored Procedure

12. Using the result from one stored procedure in a second stored procedure

13. Using Results Of Stored Procedure As Input Into Another Stored Procedure