Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using CASE in Transact-SQL

Status
Not open for further replies.

pthalacker

Programmer
Aug 30, 2004
150
All the examples I have seen show the CASE statment or function returning a column in a result set. I want to conditionally set the value of a variable for use in a WHERE clause. Can CASE be used programatically to assign a value to a variable (or to control program flow) or do I have to use a series of IF...THEN...ELSE statements?

pamela
 
Code:
select @myvariable = 
case when something = something then something
else somethingelse
end
hope this helps

Alex

Ignorance of certain subjects is a great part of wisdom
 
In most cases you don't need CASE in WHERE clause, whit simple boolean statements you can do what you want.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Alex,
Perfect, exactly what I am after. Seems obvious now that you demonstrate it.

Borislav,
Yes, I only need a Boolean comparison in my Where clause.

Code:
Date > @BeginningDate

but I need to conditionally determine the value of @BeginningDate based on a passed parameter and today's date.

pamela
 
You could use this:
Code:
CREATE PROCEDURE prcTestMe(
@BeginningDate as datetime = NULL
)
AS
  SELECT......
        WHERE (@BeginningDate IS NULL OR Date > @BeginningDate)
of course I am not sure if that will works for you, becuase with only that statement I can't understand what happens if you didn't pass @BeginningDate as parameter.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Hi Borislav,

The parameter passed will be to indicate whether we need to go back 1,2 or 4 billing cycles from today (GetDate())

pamela
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top