Treating MIN and MAX as non-scalar

kalokalo Posts: 89 Bronze 5
i'm getting a EI003 warning (Subquery may return more than one row.) for the following code where only one value would be returned; putting TOP 1 {} ORDER BY round it seems like overkill just to remove warning

DECLARE @foo INT = (
SELECT (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])
)
SELECT @foo
Tagged:

Best Answers

Answers

  • kalokalo Posts: 89 Bronze 5
    Thanks Jessica I shall add a request : it seems I get the warning even when I and assign a variable to the result of a created scalar function
  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    Thanks @kalo!

    Would it be possible to get a copy of the create script for one of the scalar functions that are still getting the warning?

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kalokalo Posts: 89 Bronze 5
    Hi Jessica, seems it occurs only if i wrap use a select in the right hand side of the statement assigning the value so I can remove the warning by removing the SELECT keyword.

    Example:

    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)

  • kalokalo Posts: 89 Bronze 5
    Therefore i can remove the warning from MIN/MAX also by not initializing when i declare 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
Sign In or Register to comment.