You can do this with a view over a table-valued function. This looks something like:
-- === Table-Valued function ====================================
--
create function fn_foo (
@Date datetime
) returns @ResultSet table (
DateKey datetime
,DisplayDate varchar (20)
) as
insert @ResultSet (
DateKey
,DisplayDate
)
select DateKey -- Just pretend there's something to select
,DisplayDate -- behind the scenes
from ods.Dates
where DateKey <= @Date
return
go
-- === View ============================================
--
create view vw_foo (
DateKey
,DisplayDate
) as
select DateKey
,DisplayDate
from fn_foo ('2009-04-31')
go
There are a few caveats:
There are some limits to what you can do with functions. In particular there is something of an impedance mismatch between stored procedure code and function code so you typically can't use a function to wrap around a stored procedure for the purposes of doing this sort of thing.
The first point means you will probably have to re-cast your stored procedure as a table-valued function.