October 25, 2022 by Kenneth Fisher
Some people have shower thoughts, I have 1am thoughts. In this case it was the only keyword required in a select statement is SELECT. This is probably pretty basic to most of you but you can do simple things like this:
Display one or more pieces of information.
DECLARE @HelloWorld nvarchar(20) = 'Hi!'; SELECT @HelloWorld AS [Display A Variable]; SELECT 4-2 AS [Do Math], GetDate() AS [Run A Function]; SELECT @@Language AS [System Variables], 'Etc' AS [Literals];
SET one or more variables.
DECLARE @MyName varchar(20); DECLARE @Counter int; SELECT @MyName = 'Ken', @Counter = 0;
My epiphany of the night though was that you could have a SELECT statement with a WHERE clause but no FROM. I’m not entirely sure why this excited me so much. All I can do is point out that it was 1am and I am not a night person. Either way, this works.
DECLARE @Hi nvarchar(20) = 'Hi!'; SELECT @Hi AS [Good Morning] WHERE datepart(hour,getdate()) BETWEEN 6 AND 11; SELECT @Hi AS [Good Afternoon] WHERE datepart(hour,getdate()) BETWEEN 12 AND 18;
So why would you want to do this? The best I can come up with is a conditional SET statement. But an IF statement and a SET statement will work just as well and probably be more understandable. I thought it might be useful to as a conditional information display, for example if you have a print variable set, but since it displays the column information regardless it wouldn’t work the way you’d want it to, and again, an IF statement outside the SELECT will work better.
I have to say that while I’m still highly amused by the idea of a SELECT/WHERE only statement, as far as I can tell it’s pretty useless. Although I’d be more than happy to be proven wrong.