[MSSQL] stored proc output combineren met data uit tabel

[Verwijderd]

Database: MS SQL Server 2000

Voor rapportage heb ik een hoop rapporten nodig die voor elke dag in een periode een record teruggeven, ook al zijn er voor die datum geen records in de tabel(len) te vinden.
Simpel op te lossen, dacht ik. Dan schrijf ik tog een stored proc die een recordset teruggeeft met alle data daarin (uitgeklede versie staat hieronder), en join die dan met de werkelijke data, maar helaas...

De stored proc werkt prima, maar ik kan z'n resultset op geen enkele manier joinen met gegevens. Behalve binnen een nieuwe stored proc, maar dat doe ik liever niet, want het gaat om tientallen rapporten die net even wat andere gegevens moeten tonen.

In InterBase was dat simpel:

code:

select D.CAL_DATE, M.REVENUE
from udp_calendar('01.04.2004', '30.04.2004') D
left join MANAGEMENT_SUMMARY M
on M.SUMMARY_DATE = D.CAL_DATE

...maar tot nu toe is me dit in MSSQL nog niet gelukt, ook niet naar lang zoeken in de BOL, op MSDN en op Google.

Via een view gaat 't ook niet, want een stored proc moet (voor zover ik weet) binnen MSSQL uitgevoerd worden via het 'exec' commando, en views staan dat commando niet toe.

Hopelijk heeft hier iemand een slim idee, want het vooruitzicht om 50+ stored procjes bij te gaan moeten houden trekt me niet zo heel erg. Vooral niet als 't niet nodig blijkt te zijn.

Transact-SQL:

CREATE procedure udp_calendar
  @STARTDATE datetime = null,
  @ENDDATE datetime = null
as
declare
  @theDate datetime,
  @dNow    datetime
begin
  set nocount on
  declare @calendar table(CAL_DATE datetime)
  select @dNow = getdate()
  -- remove time from the current date (there must be a simpler way...)
  select @dNow = cast(datepart(yyyy, @dNow) as varchar(4)) + '-' + 
                 cast(datepart(mm, @dNow) as varchar(2)) + '-' + 
                 cast(datepart(dd, @dNow) as varchar(2))

  -- default to 2 years back and 2 years in the future
  if (@STARTDATE is null)
    select @STARTDATE = dateadd(yyyy, -2, @dNow)
  if (@ENDDATE is null)
    select @ENDDATE = dateadd(yyyy, +2, @dNow)

  select @theDate = @STARTDATE
  while (@theDate <= @ENDDATE) begin
    insert into @calendar
      (CAL_DATE)
    values
      (@theDate)
    select @theDate = @theDate + 1
  end

  -- return the result
  select CAL_DATE
  from @calendar
  order by CAL_DATE
end
GO

[whoami]

Maak ipv een stored procedure een table-valued UDF (User Defined Function), die kan je dan in een select gebruiken.

[Verwijderd]

UDF had ik ook al geprobeerd, maar binnen een UDF mag je geen temp tables gebruiken ('declare @calendar table(...)' of 'create table #calendar(...)').

[edit]
Werkt inderdaad, dank je. Als je de table maar definieert in de RETURNS clause, en niet in de body van de function.
't Is nog niet helemaal zoals ik het wil hebben, want in de "echte" versie wordt ook nog een temp table misbruikt als een array/stringlist/collection, maar daar kom ik denk ik wel uit.

Wel jammer dat dit niet onder MSSQL 7 zal gaan werken (die kent geen UDFs), want daar hebben we ook nog dik 100 klanten van...

[ Voor 62% gewijzigd door Verwijderd op 29-07-2004 19:12 ]