Many of us are tend to overlook simple things even if we are capable of doing complex work. In SQL Server 2008, inline variable assignment is available. This feature exists from last 3 years, but I hardly see its utilization. One of the common arguments was that as the project migrated from the earlier version, the feature disappears. I totally accept this argument and acknowledge it. However, my point is that this new feature should be used in all the new coding – what is your opinion?
The code which we used in SQL Server 2005 and the earlier version is as follows:
DECLARE @iVariable INT, @vVariable VARCHAR(100), @dDateTime DATETIME
SET @iVariable = 1
SET @vVariable = 'myvar'
SET @dDateTime = GETDATE()
SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT
GO
The same should be re-written as following:
DECLARE @iVariable INT = 1, @vVariable VARCHAR(100) = 'myvar', @dDateTime DATETIME = GETDATE()
SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT
GO
I have started to use this new method to assign variables as I personally find it very easy to read as well write.
Do you still use the earlier method to declare and assign variables? If yes, is there any particular reason or just an old routine? I am interested to hear about this.
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
This is a simple but interesting topic Pinal. I wrote about this sometime back over my blogs too – .. There are some hidden things that can get missed too.
Nevertheless, it is a lerning opportunity everytime :).
Vinod
Thanks Pinal,
This is really a very helpful information
I follow the traditional/old method. Declare with initializer seems interesting will have to apply it and see how it works out.
Hello, Pinal!
We use in-line declaration and initialization for any new code that we write. As you mentioned, our excuse for not going back and rectifying old code is simply “If it isn’t broken, don’t go and fix it”. However, all our checklists have been updated to check for usage of newer features whenever a particular piece of code is modified.
However, for data cleansing scripts which remain the same for some versions of our product (and if these versions also run on older SQL Server versions), we use the conventional methods of declaration & initialization.
Word of caution though – this feature might cause developers to declare and use variables as they think about them (i.e. in the middle of the script). That is one practice that should be avoided.
Vinod: Thank-you for the link to your post. It was great reading!
To the readers: If you haven’t been to Vinod’s post, please go there now – it gives you a very good reason why declaration & assignment also need to be part of the TRY…CATCH block.
I find traditional declaration and initialization easier visually, especially in a team environment where you may be working with unfamiliar code. Discrete statements “stand out” more, as would headings in a written document.
At the same time, I can appreciate the utility of this approach, and see instances where it would be more robust, e.g. inclusion in a try/catch block as mentioned above.
Thanks for the good post.
DECLARE @iVariable INT = 1, @vVariable VARCHAR(100) = ‘myvar’, @dDateTime DATETIME = GETDATE()
SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT
GO
Not working in SQL SErver 2005 Getting the error “Cannot assign a default value to a local variable. ”
Please update your post…
This is specific from versions later than 2005. You need to assign the values after declaring them
thanks ver good
Hi i created a temp table but i want to create for below code
DECLARE @StartDate DATETIME
,@EndDate DATETIME;
SELECT @StartDate =’20100101′
,@EndDate = GETDATE()
–convert(varchar(8), GETDATE(),112);
;With DT_TempTbl as
(
SELECT @StartDate AS YearMonth
UNION ALL
SELECT DATEADD(MONTH, 1, YearMonth) FROM DT_TempTbl
WHERE YearMonth <=@EndDate
)
select convert(varchar(6),YearMonth,112) AS YearMonth1 FROM DT_TempTbl order by YearMonth1
Please help me to creating function
Hello,
I have a requirements like I have 4 variable and want the result like var1 and var2….
If any variable is not having any value, it should skip that part. How I can do this?
I had been doing the old way as I never knew you could do that until the other day.