How to use a constant value in select query in a view definition

Question

Post reply

How to use a constant value in select query in a view definition

Manoj Wadhwa

Old Hand

Points: 320
More actions
October 18, 2007 at 12:10 pm

#69264

I have a view definition as below.
create view dbo.view1 as
select * from table1
where date1 >= '2007-01-01' or
date2 >= '2007-01-01' or
date3 >= '2007-01-01'
I'd like to define the date value '2007-01-01' as a constant and reframe the query like this :
create view dbo.view1 as
select * from table1
where date1 >= @dateConstant or
date2 >= @dateConstant or
date3 >= @dateConstant
I'd like to know how and where to define this @dateConstant and whether or not it's possible to do so.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i might be second guessing you, but i believe you want to change the date to a PARAMETER, and not a CONSTANT, right?

If my re-interpretation is right, then you want to use a stored procedure (or a function that returns a table) instead:

Create Procedure prGetView1 (@dateConstant datetime)

As

begin

SET NOCOUNT ON

select * from table1

where date1 >= @dateConstant or

date2 >= @dateConstant or

date3 >= @dateConstant

END

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Manoj Wadhwa Old Hand Points: 320 More actions · Answer 2

I want to use a constant for comparision in the where clause of the query used in view.

I tried using CTE like this but this too fails as after myCTE it expects 'ON':

create view dbo.view1 as

with myCTE as( select cast('20070101' as dateTime) as dateConstant )

select * from table1 t1 inner join table2 t2 on t1.joinCol1 = t2.joinCol2

inner join myCTE

where date1 >= dateConstant or

date2 >= dateConstant or

date3 >= dateConstant

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Another name for "View with parameters" is "Table function".

_____________
Code for TallyGenerator

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 4

I think Manoj wants a constant. Not a parameter.

Best Regards,

Chris Büttner

Lowell SSC Guru Points: 323518 More actions · Answer 5

the way to do that would be with a scalar function then:

Create Function dateConstant ()

returns datetime

AS

begin

return '01/01/2007'

end

select dbo.dateConstant()

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

Or use a CTE as described here:

http://www.sqlservercentral.com/Forums/Topic412364-145-1.aspx#bm412471

Best Regards,

Chris Büttner

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

:blink::ermm::Whistling: The constant is also a LITERAL in this case... it's not like they're selecting it or anything... the view is simple...

create view dbo.view1 as

select * from table1 t1 inner join table2 t2 on t1.joinCol1 = t2.joinCol2

inner join myCTE

where date1 >= '20070101' or

date2 >= '20070101' or

date3 >= '20070101'

...takes less code, too! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)