create view

CREATE VIEW dbo.viewNeedPrices
AS
SELECT equityID, tickerRealTick
FROM dbo.equities
WHERE (equityID NOT IN
(SELECT equityID
FROM prices
WHERE priceDate = '1/1/2004'))
This view works.
I want to be able to do this without hardcoding the date.
I tried using '?'. DIdn't work.
Any way to do this?Hi Won,
On SQL Server 2000 you can use a table valued user defined function:
CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
RETURNS TABLE
AS
RETURN
SELECT equityID, tickerRealTick
FROM dbo.equities
WHERE (equityID NOT IN
(SELECT equityID
FROM prices
WHERE priceDate = @.priceDate))
Jacco Schalkwijk
SQL Server MVP
"Won Lee" <noemail@.nospam.com> wrote in message
news:%23qvAkpc5DHA.2300@.TK2MSFTNGP10.phx.gbl...
> CREATE VIEW dbo.viewNeedPrices
> AS
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = '1/1/2004'))
> This view works.
> I want to be able to do this without hardcoding the date.
> I tried using '?'. DIdn't work.
> Any way to do this?
>|||Jacco Schalkwijk wrote:
> Hi Won,
> On SQL Server 2000 you can use a table valued user defined function:
> CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
> RETURNS TABLE
> AS
> RETURN
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = @.priceDate))
>
Thanks will give this a try.|||Jacco Schalkwijk wrote:
> Hi Won,
> On SQL Server 2000 you can use a table valued user defined function:
> CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
> RETURNS TABLE
> AS
> RETURN
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = @.priceDate))
>
Thanks. Worked like a charm.
Won