Ok, I admit it. Sometimes the least important things are the most fun. As I try to get my blog back up and kicking again after a few months of holiday fun coupled with some dreary personal life things (a death in the family and lots of sickness/busyness, mostly,) I felt the need to write about another little time saving feature that you might not have heard of. Ever if you have it is still cool.
This topic is declaring and instantiating a value in a single statement. So what used to be:
DECLARE @i int
SET @i = 1
Can now be:
DECLARE @i int = 1
Ho hum, I had thought earlier when I first saw this. You know, it saves me 3 keystrokes. But today, I was doing some writing in my book and I realized that it isn't just limited to literals (it had just never crossed my mind) so when I was creating my savepoint names for nested savepoints. You can use literals and functions, whatever you need.
DECLARE @savepoint nvarchar(128) = cast(object_name(@@procid) AS nvarchar(125)) + cast(@@nestlevel AS nvarchar(3))
All in one line of code...clean, neat. And when I was writing a trigger that I could do this:
DECLARE @rowsAffected int = @@rowcount, --stores the number of rows affected
@msg varchar(2000) = '' --used to hold the error message
And save a couple of lines of code, and now, how much cleaner and effective is this code? Not only does it save two lines of code to do the SET (or you could use SELECT), but it saves lines of whitespace too.
Will this save a lot of time? Probably not, but it is one of those long desired features that we SQL Server programmers are so glad to be getting. In my case it is going to cost me time because I have to go back and re-edit places where I forgot about this syntax and didn't use it in my new book, which is one of the worst parts of writing. When you mess up and forget something that is new and more or less essential, you can have to edit large amounts of code/text.
The comment from Steve got me thinking if you could use a query to instatiate the value. Survey says:
create table fred
insert into fred
declare @value char(1) = (select MAX(value) from fred)
YES! This returns 'c', just as you would expect. Okay, so now this is even cooler. It doesn't work with table types though. And since we are feature complete, I assume it probably won't (I didn't expect it too, but it never hurts to try.)