Using Variables in Views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using Variables in Views

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-04-29 : 07:17:04

Can someone tell me if you can use variables in views. I have a user defined function that returns a date. I use this date as a filter on the table to create the view.

If I create the select statement on its own I have no problems but when I try to create the view using the same statement it keeps on giving me an error. here is the sql statement :

declare @startdate datetime

select @startdate = common.dbo.WorkingDaysAgo(getdate(),7)

select * from (table a) (NOLOCK)
where pricechangedate >= @startdate

Any help would be appreciated. If you cannot use variable in views can someone tell me how I can achieve the same result another way.

Thanxs

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-04-29 : 07:50:11

Only the SELECT statement is allowed in a view.
Try this:

CREATE VIEW testFunction
AS
SELECT *
FROM sales (NOLOCK)
WHERE pricechangedate >= dbo.WorkingDaysAgo(7)
GO

select * from testFunction

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-04-29 : 07:55:57

I have tried this but the query takes too long to run there must be a way to do this.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-29 : 09:00:12

We had a thread about this a while back:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12994
It goes off the point for a bit, but gets dragged back.

I think the answer is that if it tries to find pricechangedate with a Scan operator (table or index), it can't put the UDF call into the WHERE() predicate, and consequently ends up calling it (in a Filter operator) for every row coming from the Scan.

On the other hand, if it seeks then it can put the UDF call in the SEEK() predicate. This probably isn't much use unless you can reasonably make a covering index (or cluster on) pricechangedate and you don't get caught out by seeks on other conditions...

I don't think we really came up with a good solution.

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-29 : 10:14:22

You can do this by creating a User-Defined Function that returns a table.


set nocount on
go

if exists (select * from dbo.sysobjects 
      where id = object_id(N'[dbo].[WorkingDaysAgo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[WorkingDaysAgo]
GO

CREATE FUNCTION WorkingDaysAgo  (@DATE datetime, @DaysAgo int)
RETURNS datetime
AS
begin
   RETURN(DateAdd(d,0-@DaysAgo,@DATE))
end
GO

Create table tmp (
   RowID int identity(1,1) not null primary key clustered,
   Item varchar(20) not null,
   PriceChangeDate datetime null
)

insert tmp (Item, PriceChangeDate) values ('Toaster', NULL)
insert tmp (Item, PriceChangeDate) values ('Sofa', '2002-04-05')
insert tmp (Item, PriceChangeDate) values ('Chair', NULL)
insert tmp (Item, PriceChangeDate) values ('Bed', '2002-04-15')
insert tmp (Item, PriceChangeDate) values ('Hammer', '2002-04-25')
insert tmp (Item, PriceChangeDate) values ('Saw', NULL)
insert tmp (Item, PriceChangeDate) values ('Lamp', '2002-04-26')
insert tmp (Item, PriceChangeDate) values ('Razor', '2002-04-27')
go



if exists (select * from dbo.sysobjects 
      where id = object_id(N'[dbo].[PriceChangeItems]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[PriceChangeItems]
GO

CREATE FUNCTION PriceChangeItems (@Date datetime , @DaysAgo int)
RETURNS @retPriceChangeItems Table (
      RowID int not null primary key clustered,
      Item varchar(20) not null,
      PriceChangeDate datetime null
   )
AS

BEGIN
   DECLARE @StartDate datetime
   SELECT @StartDate = dbo.WorkingDaysAgo(@Date, @DaysAgo) 

   Insert @retPriceChangeItems
   Select *
   from tmp (nolock)
   where PriceChangeDate >= @StartDate

   RETURN
END
GO

-- Example invocation
Declare @StartDate datetime
select @StartDate = GetDate()
SELECT * from dbo.PriceChangeItems (@StartDate, 7) as TheData
-- this line does NOT work: must not be able to pass a function to a function?!?!?
-- SELECT * from dbo.PriceChangeItems (GetDate(), 7) as TheData
GO

drop function [dbo].[WorkingDaysAgo]
drop function [dbo].[PriceChangeItems]
drop table tmp

-- here is the output
RowID       Item                 PriceChangeDate
----------- -------------------- ----------------------- 
5           Hammer               2002-04-25 00:00:00.000
7           Lamp                 2002-04-26 00:00:00.000
8           Razor                2002-04-27 00:00:00.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources