March 31, 2018 at 1:38 pm
Comments posted to this topic are about the item Storing a variable with a SELECT, and why it didn't work.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 2, 2018 at 5:57 am
I use an @@ROWCOUNT after my use of SELECTs to populate a variable. IF @@ROWCOUNT=0 then do something. I think I like that better than prepopulating, but it might be six of one, half dozen of the other.
April 2, 2018 at 6:16 am
Hello there,
this is a nice article, showing the danger of this kind of SQL code.
Actually there are many people not knowing the detailled behaviour of this code, especially the part where the select returns no rows. It's kind of logical, we just are not aware of it when using it.
I personally try to avoid this code at all, mainly for 2 reasons:
1) unexpected behaviour, as this article is about
2) performance: if the select is running against a huge table it really makes difference
If I have to assign only 1 variable I use a subselect, as it really assigns null to the variable when no row is returned, or an exception, if more than one is returned (an exception I want to have to get to know of the situation).
If I have to deal with assigning multiple variables, my first thought is:
Do I really need variables, or can I do it using temp tables or table variables?
Many of us tend to not think of tables, when "needing just 2 or 3 variables". But try it, you will discover new ways of solving queries.
April 2, 2018 at 7:39 am
yap.. learned these nuances the hard way, long time ago. thanks for writing it up and sharing.
April 2, 2018 at 8:46 am
What about:
DECLARE @Date DATE = COALESCE((SELECT [Some Date]
FROM [Some Table]
WHERE [Some Key] = 'Key Value'),'1900-01-01')
This lets you declare, set, and default the variable in one statement. You don't have to worry about no rows. As for multiple rows that's way keys are for... :hehe:
April 2, 2018 at 8:54 am
You don't have to worry about no rows.
The row count acts as a kind of error handling. I only use these when there should be a row, but it wouldn't be inconceivable that there's not a row. In that case, I want it to do something. A common case--the variable is used to update a row for some ETL auditing. But if the row count is zero, an insert statement will fire instead. This wouldn't help because I'd still have to test for a variable.
April 2, 2018 at 8:56 am
roger.plowman - Monday, April 2, 2018 8:46 AMWhat about:
DECLARE @Date DATE = COALESCE((SELECT [Some Date]
FROM [Some Table]
WHERE [Some Key] = 'Key Value'),'1900-01-01')This lets you declare, set, and default the variable in one statement. You don't have to worry about no rows. As for multiple rows that's way keys are for... :hehe:
😀 Certainly you can do it that way. In fact I recently fixed some code doing exactly that.
FYI in your code, technically you are actually initializing the variable, not setting it, just with a subselect. You can also do it as a SET later, but again, you are using a subquery not actually setting the variable using a SELECT statement 🙂 And sometimes you just need multiple variables.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 3, 2018 at 3:56 am
Covering the += operator might have been a nice way of demonstrating whats happening.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-equal-transact-sql
April 3, 2018 at 7:57 am
Covering the += operator might have been a nice way of demonstrating whats [sic] happening.
Is this better than the CONCAT function? I use that exclusively now for string concatenation.
April 3, 2018 at 8:25 am
RonKyle - Tuesday, April 3, 2018 7:57 AMCovering the += operator might have been a nice way of demonstrating whats [sic] happening.
Is this better than the CONCAT function? I use that exclusively now for string concatenation.
I use CONCAT where convenient. Sometimes it has trouble with collations. Try these two queries:select 'type: ' + type_desc from sys.indexesselect concat('type: ', type_desc) from sys.indexes
to see what I mean
April 3, 2018 at 8:26 am
RonKyle - Tuesday, April 3, 2018 7:57 AMCovering the += operator might have been a nice way of demonstrating whats [sic] happening.
Is this better than the CONCAT function? I use that exclusively now for string concatenation.
CONCAT is a row context operation.
STRING_AGG is a column context operation and similar in affect to what the += operator will do when used in a select @var += query.
However the point I made wasn't about the best or worse string concatenation methods. It's was about demonstrating to the reader what is happening when attempting a SELECT @var = on a query that returns multiple rows. Just a suggestion, that's all.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply