|
|
You're in the
sql server mseq
group:Using Stored Procedure returned fields in another Stored Proc or Query
sql server mseq:
Hi all,
I've got a Join All stored proc which returns about 120,000 records. It simpley returns Account number field and Balance field for each acustomer ccount for each table. I want to use the returned data as if it were just another table or view. As in: Select * from spGetAllAccountBalances or Retrieving the customers Credit Limit and linking to the customers AccountBallance: SELECT spGetAllAccountBalances.Acct, spGetAllAccountBalances.Bal, Customer.CreditLimit, FROM dbo.Customer INNER JOIN spGetAllAccountBalances ON Customer.Acct = spGetAllAccountBalances.Acct I just can't get at the stored proc data... I don't mind doing this thru a stored procedure but would prefer to use in a view... Any help would be appreciated, Thanks BGS ----------------- CREATE PROCEDURE spGetAllAccountBalances @year as char(4), @period as char(2) AS SELECT A.ACCT, A.BAL FROM dbo.A WHERE CAST(RTRIM(A.FISCYR) + RTRIM(A.FISCPER) AS INT) <= CAST(@year + @period AS INT) UNION ALL SELECT B.ACCT, B.BAL FROM dbo.B WHERE CAST(RTRIM(B.FISCYR) + RTRIM(B.FISCPER) AS INT) <= CAST(@year + @period AS INT) UNION ALL ----- Table c,d,e, etc... GO
Hi,
Have a look into Inline table valued User defined functions in books online -- Thanks Hari MCDBA [quoted text, click to view] "BGS" <anonymous@discussions.microsoft.com> wrote in message news:1e13a01c454da$f0d96800$a501280a@phx.gbl... > Hi all, > I've got a Join All stored proc which returns about > 120,000 records. > > It simpley returns Account number field and Balance field > for each acustomer ccount for each table. > > I want to use the returned data as if it were just > another table or view. As in: > > > Select * from spGetAllAccountBalances > > or Retrieving the customers Credit Limit and linking to > the customers AccountBallance: > > > SELECT > spGetAllAccountBalances.Acct, > spGetAllAccountBalances.Bal, > Customer.CreditLimit, > FROM > dbo.Customer > INNER JOIN > spGetAllAccountBalances > ON > Customer.Acct = spGetAllAccountBalances.Acct > > I just can't get at the stored proc data... > > I don't mind doing this thru a stored procedure but would > prefer to use in a view... > > Any help would be appreciated, > > Thanks > BGS > > > > > ----------------- > > > CREATE PROCEDURE spGetAllAccountBalances > @year as char(4), > @period as char(2) > AS > SELECT > A.ACCT, A.BAL > FROM > dbo.A > WHERE CAST(RTRIM(A.FISCYR) + RTRIM(A.FISCPER) AS INT) > <= CAST(@year + @period AS INT) > UNION ALL > > SELECT > B.ACCT, B.BAL > FROM > dbo.B > WHERE CAST(RTRIM(B.FISCYR) + RTRIM(B.FISCPER) AS INT) > <= CAST(@year + @period AS INT) > UNION ALL > ----- Table c,d,e, etc... > > GO >
Thanks for the reply, Hari.
Just the name seems to elude to an extremely advanced solution. Searched the MS Online Support for: Inline table valued User defined functions but found no related documents Searched google for same and found http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies. htm would this be what you are suggesting? Thanks,
Thanks for the clarification Hugo.
I was getting Start | All Programs | Microsoft SQL Server | Books On- line mixed up with Internet Explorer | Help | Online Support
Yes, much better.
Just had another idea. Perhaps I could group my records in my previously written(enclosed in origianl post) stored proc? I can't get the stored proc to accept my grouping... Anyone know if I can use grouping with my Stored Proc? Or should I do it with the returned table? Thanks,
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,
On Sun, 20 Jun 2004 16:29:36 -0700, <anonymous@discussions.microsoft.com>
[quoted text, click to view] wrote: >Thanks for the reply, Hari. > >Just the name seems to elude to an extremely advanced >solution. > >Searched the MS Online Support for: > Inline table valued User defined functions > >but found no related documents > > >Searched google for same and found >http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies. >htm >would this be what you are suggesting? > >Thanks, > Barry G. Sumpter Hi Barry, In Books Online, activate the Index tab, enter the keyword "user-defined functions" (without the quotation marks) and select the sub-heading "return a table". Best, Hugo --
On Mon, 21 Jun 2004 14:27:07 -0700, <anonymous@discussions.microsoft.com>
[quoted text, click to view] wrote: >Yes, much better. > >Just had another idea. Perhaps I could group my records >in my previously written(enclosed in origianl post) >stored proc? > >I can't get the stored proc to accept my grouping... > >Anyone know if I can use grouping with my Stored Proc? > >Or should I do it with the returned table? > >Thanks, > baz Hi baz, Both stored procedures and functions allow the use of GROUP BY in the enclosed queries. If you post the code you used and the error message you got, I might be able to help you. Best, Hugo --
On Mon, 21 Jun 2004 18:15:43 -0700, <anonymous@discussions.microsoft.com>
[quoted text, click to view] 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 --
Don't see what you're looking for? Try a search.
|
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |