|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is it possible to join the resultsets of two stored procedures?Hi there,
I want to use the stored procdures as parametrized views: for each table with historical information (and columns StartDate smalldatetime, EndDate smalldatetime) I want to create a stored procedure. Those procedures have a parameter 'Timepoint smalldatetime' and fetch all records from the corresponding table whith 'Timepoint BETWEEN StartDate AND EndDate'. To generate reports with data from this database I would like to join the resultsets of those stored procedures (only INNER JOIN), because my data model shows 1:n and n:m relationships among those tables. Any help will be greatly appreciated! Sincerly yours, a curious sql-trigger Curious Trigger (Curious_Trigger@nospam.gmx.net) writes:
> I want to use the stored procdures as parametrized views: for each table Maybe you should implement the procedures as table-valued functions instead.> with historical information (and columns StartDate smalldatetime, > EndDate smalldatetime) I want to create a stored procedure. Those > procedures have a parameter 'Timepoint smalldatetime' and fetch all > records from the corresponding table whith 'Timepoint BETWEEN StartDate > AND EndDate'. > > To generate reports with data from this database I would like to join the > resultsets of those stored procedures (only INNER JOIN), because my data > model shows 1:n and n:m relationships among those tables. In fact, inline table-valued functions are really parameterised views with a different name. Then again, you procedures may have processing which is not permitted in functions. For a more general discussion, please see this web article of mine: http://www.sommarskog.se/share_data.html. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Another solution is to use temporary table to store the procedures results.
Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9808738EC74B2Yazorman@127.0.0.1... > Curious Trigger (Curious_Trigger@nospam.gmx.net) writes: >> I want to use the stored procdures as parametrized views: for each table >> with historical information (and columns StartDate smalldatetime, >> EndDate smalldatetime) I want to create a stored procedure. Those >> procedures have a parameter 'Timepoint smalldatetime' and fetch all >> records from the corresponding table whith 'Timepoint BETWEEN StartDate >> AND EndDate'. >> >> To generate reports with data from this database I would like to join the >> resultsets of those stored procedures (only INNER JOIN), because my data >> model shows 1:n and n:m relationships among those tables. > > Maybe you should implement the procedures as table-valued functions > instead. > In fact, inline table-valued functions are really parameterised views with > a different name. > > Then again, you procedures may have processing which is not permitted in > functions. > > For a more general discussion, please see this web article of mine: > http://www.sommarskog.se/share_data.html. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks to both of you for your replies!
I think I prefer table valued functions: I need the results only to generate the reports and I hope, functions process faster then copying all the resultsets in temporary tables and joining these afterwards. Sincerly yours, Curious Cursor Show quote "Sylvain Devidal" <administra***@manga-torii.com> schrieb im Newsbeitrag news:81DA555A-3E6A-47B6-A1CF-A9F10F8DBEAB@microsoft.com... > Another solution is to use temporary table to store the procedures > results. > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9808738EC74B2Yazorman@127.0.0.1... >> Curious Trigger (Curious_Trigger@nospam.gmx.net) writes: >>> I want to use the stored procdures as parametrized views: for each table >>> with historical information (and columns StartDate smalldatetime, >>> EndDate smalldatetime) I want to create a stored procedure. Those >>> procedures have a parameter 'Timepoint smalldatetime' and fetch all >>> records from the corresponding table whith 'Timepoint BETWEEN StartDate >>> AND EndDate'. >>> >>> To generate reports with data from this database I would like to join >>> the >>> resultsets of those stored procedures (only INNER JOIN), because my data >>> model shows 1:n and n:m relationships among those tables. >> >> Maybe you should implement the procedures as table-valued functions >> instead. >> In fact, inline table-valued functions are really parameterised views >> with >> a different name. >> >> Then again, you procedures may have processing which is not permitted in >> functions. >> >> For a more general discussion, please see this web article of mine: >> http://www.sommarskog.se/share_data.html. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >  |
|||||||||||||||||||||||
Other interesting topics