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

• Manoj Wadhwa

  Old Hand

  Points: 320

  More actions

  October 18, 2007 at 12:13 pm

  #76799

  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.

• Christian Buettner-167247

  SSChampion

  Points: 13729

  More actions

  October 18, 2007 at 12:22 pm

  #743252

  Is this something for you?

  CREATE VIEW dbo.MyView AS

  WITH MyCTE AS (

  SELECT CAST('20070101' AS datetime) DateConstant

  ,T.*

  FROM dbo.MyTable T

  )

  SELECT *

  FROM MyCTE

  WHERE date1 >= DateConstant

  OR date2 >= DateConstant

  OR date3 >= DateConstant

  Best Regards,

  Chris Büttner

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  October 18, 2007 at 4:36 pm

  #743333

  Most of us get pretty ticked off about people that multipost...

  http://www.sqlservercentral.com/Forums/Topic412362-5-1.aspx

  --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)