Subquery may return more than one row when filtering on primary key.
At the moment a warning is given "Non-scalar subquery in place of a scalar" when trying to assign a variable to the result of a query filtered by the primary key column. In this case only one result can be returned so the warning appears inaccurate and wrapping the result in a TOP 1 { } ORDER BY statement seems an overkill just to remove the warning. This is kind of a special case however where the subquery contains only one table filtered with "=" on the primary key column, but it is a very common one.
Example:
DECLARE @X = (SELECT ColumnX FROM dbo.Table WHERE PrimaryKeyColumn = @Y).
Linus
shared this idea
-
Anonymous
commented
Linus, I understand you here. For what it's worth, I use tons of scalar UDFs in my TSQL. Interestingly, when I use a specific format for my UDF calls, the same non-scalar warning will be generated, for example:
DECLARE @BrowserId INT = (SELECT [dbo].[Get.Browser.Id.By.Name](@BrowserName));
In this case, an ID is being returned for a specific browser residing in a table for which a unique constraint is placed on the browser name so only one result is possible, but this call format will produce the non-scalar warning you mention. Using another format, which I always use whenever possible does not produce the warning, although the output result is the same:
DECLARE @BrowserId INT = [dbo].[Get.Browser.Id.By.Name](@BrowserName);
I'm not suggesting that you wrap all your calls in UDFs. I want to share my experience with the same issue and how my incidental habit of turning TSQL into my own brand of language via encapsulating common calls for my procedures into application-specific functions somehow circumvents this issue with a slight difference in syntax. I don't pretend to know exactly why the syntax difference works, but I want to put it out here in case it helps you or anyone else.