join stored procedure and view

Hi everybody
I have this stored procedure called flydate
CREATE PROCEDURE FlyDate AS
declare @.gencalendar table (cal_date datetime primary key)
declare @.p_date datetime
set @.p_date =getdate()

while @.p_date > DateAdd(mm, -3, GetDate()) BEGIN
insert into @.gencalendar(cal_date)
VALUES(@.p_date)

--getdate
SET @.p_date = DateAdd(d, -1, @.p_date)
END

select cal_date AS KF_DATE,0 AS KF_STATUS from @.gencalendar
GO

-which returns all the date for the past three month
and this is my view
CREATE VIEW dbo.rpt_Kids
AS
SELECT TOP 100 PERCENT
KF_ID,dbo.just_date_formal(KF_DATE) as KF_DATE,KF_STATUS FROM dbo.KIDS
order by Year(KF_date) DESC,Month(KF_date) DESC,Day(KF_date) DESC

just_date_formal function
CREATE FUNCTION [dbo].[just_date_formal](@.dtvalue datetime)
RETURNS nvarchar(40)
AS
BEGIN
DECLARE @.display nvarchar(40)
SET @.display = CAST(DATEPART(dd, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(mm, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(yyyy, @.dtvalue) AS nvarchar) + ', ' + CAST(DATENAME(dw, @.dtvalue) AS nvarchar)
RETURN @.display
END

this returns all the date on which error is generated.on other dates on which no error message is generated.
now i want to join both of them so that i get dates of error message and those dates also on which error message is not generated
like

23 june 2005 error
22 june 2005
21 june 2005 error

it should return all dates regardless errror is there or not

Hi,

You directly cannot JOIN the output of a stored procedure in a FROM clause. Stored procedures cannot be used in contexts that require relational expressions. I realize this seems odd at first, since you *can* return a rowset from a stored procedure. However, the reasons we disallow it in TSQL are

(a) The shape of the rowset cannot be determined ahead of time (there is no metadata anywhere that describes the table returned by a stored proc, and besides, the shape returned might depend on the run-time execution path though the proc ... if (condition) select * from somewhere else select * from somewherelese)

and (b) a stored procedure can return > 1 result set.

You have a few options available

1) You can capture the output of the stored procedure into a temporary table using the INSERT INTO EXEC syntax. The code would looks something like:

create table #tempcal(KF_DATE datetime, KF_STATUS int)
go

insert into #tempcal exec FlyDate
go

now you can JOIN on #tempcal.

2) You can refactor your proc to make it into a table-valued function, which you *can* use in a JOIN. You will have to factor-out the non-deterministic getdata() and pass them in as parameters. The code would look something like this

create function FlyDateFunc(@.p_date datetime, @.p_today datetime)
returns @.gencalendar table (KF_DATE datetime primary key, KF_STATUS int)
as
begin

while @.p_date > DateAdd(mm, -3, @.p_today)
begin
insert into @.gencalendar values (@.p_date, 0)
set @.p_date = DateAdd(d, -1, @.p_date)
end

return
end
go

select * from FlyDateFunc(getdate() , getdate())
go

Does this answer your question?
Thanks

|||

Hi,

I have an extra complication to this problem. I have SP's that create dynamic columns based on the parameters they get. So i don't know in advance what my #table should look like. Is there any way to do this without knowing the columns of the #table? (like the select * into #tmp from table1 but then using EXEC? )

I have developed SP's that create dyn columns. Now i want to use these same SP's in SSRS, but that want's to know the column names in advance. So i want to store the results of the SP in a #table and then unpivot that to send it to RS. Think that'll work?

[edit] the SP's use dynamic SQL to get the results

Regards Gert-Jan