July 7, 2011 at 9:47 am
The code in question:
DECLARE @thevar INT
SELECT @thevar = TheID
FROM TheTable
WHERE TheType = 'atype'
SET @thevar = ( SELECT TheID
FROM TheTable
WHERE TheType = 'atype'
)
I always use the first flavor and in fact have never seen the latter that I can recall. Would love to hear thoughts on this.
July 7, 2011 at 10:09 am
one big difference:
SET @thevar = ( SELECT TheID
FROM TheTable
WHERE TheType = 'atype'
)
will fail if the subquery returns more than 1 record. The SELECT will not.
In short - if you need to be sure that only one record comes through (as opposed to pulling just one of several allowable values), use the SET statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 7, 2011 at 10:20 am
Matt Miller (#4) (7/7/2011)
one big difference:
SET @thevar = ( SELECT TheIDFROM TheTable
WHERE TheType = 'atype'
)
will fail if the subquery returns more than 1 record. The SELECT will not.
In short - if you need to be sure that only one record comes through (as opposed to pulling just one of several allowable values), use the SET statement.
To be on the safe side, you could force a single result via
SELECT TOP 1 ...
in your subquery of course.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 7, 2011 at 10:20 am
On a perf wise angle. If you have multiple variables to set it will be faster to set them all in the same go rather than separate statements.
You'd only start to see a different with 1000s of iterations tho.
That was tested ions ago on sql 2000. Might not be true anymore.
July 7, 2011 at 2:20 pm
Matt, I had thought that was the case just by parsing the query but hadn't run any tests. Thanks for the confirmation.
Setting multiple values at a time isn't a workable feature for this particular query set and we're only talking about a handful of data points so performance isn't a concern.
Guess I'll let this one go for now.
Thanks all.
July 7, 2011 at 4:33 pm
You should realise, that even though using SELECT @Var = Data FROM TABLE will not crash when multiple rows are returned, the results that you get back from the query, especially if you are not including an ORDER BY clause, could be very weird.
When multiple rows are returned, SELECT @Var uses the last row returned to store the data - though if that last row is NULL, then things get even weirder.
Your best bet is to ensure that you're only getting one row back. Even using the SELECT @VAR statement, having a TOP 1 clause is not a bad idea, and putting an ORDER BY clause is also probably a good idea.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply