Re: Using Stored Procedure returned fields in another Stored Proc or Query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 06/22/04

  • Next message: Hugo Kornelis: "Re: Dealing with Nulls, - use previous record" 
    Date: Tue, 22 Jun 2004 22:22:56 +0200

    On Mon, 21 Jun 2004 18:15:43 -0700, <anonymous@discussions.microsoft.com>
    wrote:

    >CREATE PROCEDURE Create_Worksheet_Summary
    >@year as char(4),
    >@period as char(2)
    >AS
    >SELECT
    >* FROM
    > dbo.A
    >Group By A.Acct
    >WHERE CAST(RTRIM(A.FISCYR) + RTRIM(A.FISCPER) AS INT)
    ><= CAST(@year + @period AS INT)
    >UNION ALL
    >(SELECT
    >* FROM
    > dbo.B
    >Group By B.Acct
    >WHERE CAST(RTRIM(B.FISCYR) + RTRIM(B.FISCPER) AS INT) <=
    >CAST(@year + @period AS INT))
    >
    >GO
    >
    >
    >----
    >
    >Incorrect syntax near 'groupby' (or Group by)
    >
    >Thanks,
    > Baz

    Hi Baz,

    You got the clauses in the wrong order - group by comes after where. Use
    this instead:

    CREATE PROCEDURE Create_Worksheet_Summary
                     @year as char(4),
                     @period as char(2)
    AS
      SELECT A.Acct, other columns in aggregate functions
      FROM dbo.A
      WHERE CAST(RTRIM(A.FISCYR) + RTRIM(A.FISCPER) AS INT)
            <= CAST(@year + @period AS INT)
      GROUP BY A.Acct
      UNION ALL
      SELECT B.Acct, other columns in aggregate functions
      FROM dbo.B
      WHERE CAST(RTRIM(B.FISCYR) + RTRIM(B.FISCPER) AS INT)
            <= CAST(@year + @period AS INT)
      GROUP BY B.Acct
    GO
    (untested)

    Note - besides changing the order of WHERE and GROUP BY, I also changed
    SELECT * to something more sensible. SELECT * is never a good idea in
    production code; worse is that it will not work in a query that has a
    group by clause. The only things allowed in the SELECT list if you use
    GROUP BY are the columns listed in GROUP BY or aggregate functions.

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: Hugo Kornelis: "Re: Dealing with Nulls, - use previous record"