SQL statement question - defining a variable

Factual Questions
1

I’m kind of stuck. I’m more an LINQ guy, haven’t touched much SQL in a while. I’ve got the query below where I’m retrieving records that fall within 9:00 am to 9:00pm in a particular timezone. As you can see the TimeZone entry in the DB is a string and I’m using a CASE statement to convert it to an int.

Is there some way that I can do that once and set the result to a variable I can re-use for all subsequent calls? I don’t like having to repeat it all the time.




SELECT ID, TimeZone      
 FROM dbo.Table1
WHERE CONVERT(CHAR(8),DATEADD(hour, 
CASE dbo.Table1.TimeZone
         WHEN 'East' THEN 0
         WHEN 'Central' THEN -1
         WHEN 'Mountain' THEN -2
         WHEN 'West' THEN -3
END, CURRENT_TIMESTAMP),8) > '09:00:00'
AND CONVERT(CHAR(8),DATEADD(hour, 
CASE dbo.Table1.TimeZone
         WHEN 'East' THEN 0
         WHEN 'Central' THEN -1
         WHEN 'Mountain' THEN -2
         WHEN 'West' THEN -3
END, CURRENT_TIMESTAMP),8) < '21:00:00'
AND DATEADD(hour, 
CASE dbo.Table1.TimeZone
         WHEN 'East' THEN 0
         WHEN 'Central' THEN -1
         WHEN 'Mountain' THEN -2
         WHEN 'West' THEN -3
END, CURRENT_TIMESTAMP) > appointment
2

In straight SQL, nope. You have to repeat it which does get repetitive but it works. There are types of SQL like Oracle PL/SQL that allow variables but straight SQL won’t do it.

3

Make a timezone conversion table variable and join to it

so:



DECLARE @TimeZoneMath TABLE (
  TimeZone varchar(10) null,
  HourOffset int null
)

INSERT @TimeZoneMath (TimeZone, HourOffset) values ('East', 0)

SELECT ID, TimeZone      
 FROM dbo.Table1 as t
 JOIN @TimeZoneMath as m on t.TimeZone = m.TimeZone
WHERE t.hour + m.dateoffset > appointment.

Or something like that anyway.

4

Shagnasty is right. Pure SQL is mostly declarative and doesn’t allow variables. A better way would be to define a view with the timezone already converted to an int, then run queries against that. A stored procedure (in whatever procedure language your DB uses) would also work.

5

If your SQL system supports subqueries, you could do:



SELECT ID, TimeZone
FROM
(
  SELECT ID,
    TimeZone,
    DATEADD(hour, 
      CASE TimeZone
           WHEN 'East' THEN 0
           WHEN 'Central' THEN -1
           WHEN 'Mountain' THEN -2
           WHEN 'West' THEN -3
      END, CURRENT_TIMESTAMP) AS adjusted_hour,
    CONVERT(CHAR(8), adjusted_hour, 8) AS adjusted_hour_string
  FROM db0.Table1
  WHERE adjusted_hour > appointment
) AS subq
WHERE adjusted_hour_string >= '09:00:00' AND adjusted_hour_string < '21:00:00'
6

This is the winning answer, or the start of it anyhow, if you are using Microsoft SQL Server 2000 or greater.

7

Thanks guys! I’ll give the JOIN a shot.

8

What DB Server are you using? If it’s SQL Server then you can create a user defined function that will return either 0, -1, -2, -3 depending on what string value is passed into it.

Then you can your re-write SQL to incorporate the function like this…



SELECT ID, TimeZone      
FROM dbo.Table1
WHERE CONVERT(CHAR(8),DATEADD(hour, dbo.GetTimeZone(dboTable1.TimeZone), CURRENT_TIMESTAMP) > appointment

This may be possible with other Database servers as well, but SQL Server is the only one I am familiar with.

9

Microsoft SQL Server starting with the 2005 version has something called “common table expressions” which are another way to accomplish the same thing as joining a temp table or whipping up a UDF.

In terms of functionality CTEs are about like a subquery or table-valued UDF. The one awesome thing CTEs offer which the others don’t is an elegant way to express recursive queries. This feature isn’t relavant to the OP’s immediate need, but is still good info to know for next time.

if I was the OP and had SQL Server 2005 or later, CTEs would be my first choice.

10

While creating a function is convenient, it hasto execute the function code for each row you need to calculate. By creating a table an joining to it, you cut down massively on CPU requirements.