A newbie's question about stored precedure

  • Thread starter Thread starter W. \Mark\ Xu
  • Start date Start date
W

W. \Mark\ Xu

I am an Access user. When a database got too big and Access got too slow, I
switched to a combination of access.adp and sqlserver. I use both Access
2000 and SQL 2000.

I made the first stored procedure named "stoVolume" to create a few
variables from a few tables. It worked fine. When I click the stored
procedure it produce a table of the variables for me.

Alter Procedure stoVolume

As

SELECT

([expvol]*[tpacurr]) AS TREE,

COND.LANDCLCD,

CASE WHEN [SPGRPCD] <=24 THEN 'SW' ELSE 'HW' END AS MJSPGRP



FROM

TREE INNER JOIN COND ON

(COND.PLOT = PLOT.PLOT);



return


Now I want to sum the variable TREE by groups MJSPGRP and LANDCLCD. What
should I do? The following codes didn't work:

Create Procedure stoVolSum
As
Select Sum [TREE]
FROM stoVolSum
GROUP BY MJSPGRP, LANDCLCD;
return

I know it might seem funny to you, but I am really new to the store
procedure thing. Any suggestion is appreciated.

Mark
 
W. "Mark" Xu wrote:

Alter Procedure stoVolume
As
SELECT
([expvol]*[tpacurr]) AS TREE,
COND.LANDCLCD,
CASE WHEN [SPGRPCD] <=24 THEN 'SW' ELSE 'HW' END AS MJSPGRP
FROM
TREE INNER JOIN COND ON
(COND.PLOT = PLOT.PLOT);
return

Now I want to sum the variable TREE by groups MJSPGRP and LANDCLCD. What
should I do? The following codes didn't work:

Create Procedure stoVolSum
As
Select Sum [TREE]
FROM stoVolSum
GROUP BY MJSPGRP, LANDCLCD;
return
< SNIP >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First off: I'm suprised your 1st SP worked 'cuz you have a strange
FROM clause:
FROM
TREE INNER JOIN COND ON
(COND.PLOT = PLOT.PLOT)

You don't name the TREE table in the ON clause. It should be:

FROM Tree INNER JOIN Cond On Tree.Plot = Cond.Plot

This may be a typographical error.

On your 2nd SP: SQL'r doesn't allow the use of an SP as a data source
in the FROM clause. You can use a rowset function (SQL'r 2000). An
example of your 2nd SP, as if your 1st SP was a rowset function:

CREATE PROCEDURE stoVolSum
AS
SELECT MJSPGRP, LANDCLCD, Sum(TREE) As TreeSum
FROM stoVolume
GROUP BY MJSPGRP, LANDCLCD

See the Books On Line (BOL) article on CREATE FUNCTION for a
description of how to create rowset functions. Seel the BOL SELECT
article for a description of how to use the GROUP BY clause (you have
to have all the non-aggregate columns as are in the SELECT clause in
the GROUP BY clause).

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQE+cp4echKqOuFEgEQLQJwCgxiUxlVQO5JnI4H+NP+VXmr1mxQcAoIzq
s6LhEzQ/o9mD6Y7oCIDCWn3w
=gFr1
-----END PGP SIGNATURE-----
 
Back
Top