Can you declare a case statement as a Variable

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

General SQL Server Forums

New to SQL Server Programming

Can you declare a case statement as a Variable

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-20 : 05:30:47

Hello

Can you declare a case as a Variable

for eg

declare @test = join_date <= CASE
WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-28
ELSE GETDATE()-30

MCTS / MCITP certified

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 05:47:18

[code]declare @test datetime
select @test=CASE
WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-28
ELSE GETDATE()-30
END
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 05:49:54

To be more clear...

DECLARE means to say I have a variable named ... of datatype ...
What you mean is to DEFINE a variable with a value that comes out of a CASE expression.

No, you're never too old to Yak'n'Roll if you're too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-20 : 05:51:36

would i not have to put it like this

delcare @test datetime
set @test = WHERE join_date <= CASE
WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-28
ELSE GETDATE()-30

thank you for your quick reply

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-20 : 05:57:28

sorry didnt mean to put the where clause in with the case statement

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-20 : 05:58:35

Its not clear that you want to assign @test to you join_date or GetDate()-28 or Getdate()-30.
If you want to assign join_date then there might be possible there will be more than one join date which fulfill this condition.
in this situation you need to make a decision to take top 1 or max or min.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Kristen
Test

22859 Posts

Posted - 2010-10-20 : 06:00:15

"would i not have to put it like this"

No.

You can use SET or SELECT. SET only allows you to set one variable at a time. There are some other subtle variations (which can help / hinder, see DOCs for more details).

Your "WHERE join_date <= " will cause a syntax error. Put that in the SELECT statement it refers to.

If you want a generic ability to reuse this logic then use a User Defined Function - then you can call that anywhere in your SQL code - and if you need to change it then you only have to change the User Defined Function itself

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-20 : 06:01:45

You can do like below -


DECLARE @test DATETIME
SELECT @test = max(join_date) FROM <Your Table>
WHERE join_date <= CASE WHEN DATEPART(dw, getdate()) IN (7, 1) THEN (GETDATE()-28) ELSE (GETDATE()-30) END

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 06:08:49

I remember your old post with setting inactive...
You can declare and set @test like I have shown and then you can use it instead of the CASE expression in your query.

No, you're never too old to Yak'n'Roll if you're too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-20 : 06:19:07

Thank you

I was trying to create a function as well with

create function dbo.30days (@30Day datetime)
returns datetime
as
begin
CASE
WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-28
ELSE GETDATE()-30

end

but i was getting an error in line 1 near 30.

i couldnt see what was wrong

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 07:10:55

This?

create function dbo.fn30days()
returns datetime
as
begin
return
CASE 
WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-28
ELSE GETDATE()-30
END
end

select dbo.fn30days()

No, you're never too old to Yak'n'Roll if you're too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-20 : 07:22:57

thank you, I am learning so much now.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 07:28:01

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 06:49:56

Can't have getdate() in a function I think? You'll have to pass that in as a parameter


create function dbo.fn30days(@MyDate datetime)
returns datetime
as
begin
return
CASE 
WHEN DATEPART(dw, @MyDate) IN (7, 1) THEN @MyDate-28
ELSE @MyDate-30
END
end

select dbo.fn30days( getdate() )

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-21 : 06:53:45

quote:
Originally posted by Kristen

Can't have getdate() in a function I think? You'll have to pass that in as a parameter

You should try before reply

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-21 : 06:59:01

quote:
Originally posted by webfred

quote:
Originally posted by Kristen

Can't have getdate() in a function I think? You'll have to pass that in as a parameter

You should try before reply

No, you're never too old to Yak'n'Roll if you're too young to die.

But your protest is right, when using SQL Server 2000

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 07:22:39

Ah, OK. Old news then. I can go change all my Functions to not be passed GetDate() as a parameter now .... a joyous afternoon indeed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources