SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Code Review - Scalar Variables

One very common mistake I see when reviewing code is the case where the select statement is returning multiple rows, but assigning a column to a scalar variable, like this:

select @ID = ID from SomeTable

It works, but the results aren't necessarily reliable. Probably the most common place I see this is inside a trigger, because far too many people expect a trigger to fire once per row (it fires once per batch). Sometimes the values returned are all the same so it doesn't really matter (cmon now, it will matter sooner or later), but I've also seen cases where it's choosing a value randomly. How'd you like that to be used on your checking account balance?

My rule of thumb is if Im assigning to a scalar is to always use Top 1 with an order by - which may not be perfect, but will return consistent results.


I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.


No comments.

Leave a Comment

Please register or log in to leave a comment.