EI003 to allow variable assignment of MIN and MAX and Scalar-valued function results
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 MIN or MAX query which would only result in a single value. Also get the warning when i create a Scalar valued function and assign variable to result of a call to that. In each of these 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
James Strath
shared this idea
-
James Strath
commented
Can remove the warning from MIN/MAX also by not initializing when declaring the variable , but I'd rather keep to the original syntax style
no squiggly line in this example
DECLARE @foo INT
SELECT @foo = (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])SELECT @foo
squiggly line for this preferred syntax:
DECLARE @foo INT = (
SELECT (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])
)
SELECT @foo -
James Strath
commented
IF EXISTS(SELECT (1) FROM sys.objects [o] WHERE (SCHEMA_NAME([o].[schema_id]) = (N'dbo')) AND [o].[name] = (N'fn_myfunc') AND [o].[type]=(N'FN')) DROP FUNCTION [dbo].[fn_myfunc];
GO
CREATE FUNCTION [dbo].[fn_myfunc] ()
RETURNS NVARCHAR(11)
AS BEGIN
RETURN(N'hello world')
END
GO
;DECLARE @HasWarning nvarchar(11) = (SELECT [dbo].[fn_myfunc]())
;PRINT(@HasWarning)
;DECLARE @NoWarning nvarchar(11) = ([dbo].[fn_myfunc]())
;PRINT(@NoWarning) -
James Strath
commented
eg.
DECLARE @foo INT = (
SELECT (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])
)
SELECT @foo