locked
Stored procedure returning multiple result sets RRS feed

  • Question

  • Hello, 

    I have a stored procedure that I've been tasked to add functionality to.  The stored procedure executes a select statement and returns one record, and I need to add failover to it.  

    Let's say I have the following:

    ALTER PROCEDURE dbo.myTestProc @ObjectCode @Alias @Locale @failoverLocale --Extra value to try if no records exist with the first locale AS

    BEGIN

    SELECT * from myTable WHERE ObjectCode = @ObjectCode and Alias = @Alias and Locale = @Locale if(@@ROWCOUNT = 0 AND @failoverLocale = '') BEGIN EXEC dbo.myTestProc @ObjectCode @Alias @failoverLocale '' END

    END

    This works, but it returns two result sets when the @failoverLocale is used.  To work around this, I created a TABLE variable, ran the select statement inserting the value into the variable, and using IF EXISTS() to see if it's empty.  If it's not then I just select that result variable, and if it's empty I re-run the proc with the @failoverLocale. 

    ALTER PROCEDURE dbo.myTestProc
    
    @ObjectCode
    @Alias
    @Locale
    @failoverLocale --Extra value to try if no records exist with the first locale
    AS
    BEGIN
    
    DECLARE @Result TABLE(
    	[ObjectCode] [bigint] NOT NULL,
    	[Alias] [varchar] NOT NULL,
    	[Locale] [varchar] NOT NULL
    	)
    
    INSERT INTO @Result
    SELECT * from myTable
       WHERE
         ObjectCode	= @ObjectCode and
         Alias			= @Alias and
         Locale		= @Locale
    
    IF EXISTS (SELECT * FROM @Result)
    BEGIN
    	SELECT * FROM @Result
    END
    
    ELSE IF(@failoverLocale = '')
    BEGIN
    	EXEC dbo.myTestProc 
    		@ObjectCode
    		@Alias
    		@failoverLocale
    		''
    END
    END

    This also works, and returns only one result set, but this is a stored procedure that is run VERY frequently, and it has to be optimized for the best performance possible.  I'm really looking for a more elegant way of accomplishing what I need to.

    Thanks,

    Nick


    Thursday, June 5, 2014 8:46 PM

Answers

  • I think this is the best you can do. You can, of course, change the query to use 

    IF EXISTS (select from myTable where ObjectCode = @ObjectCode and Alias = @Alias and Locale = @Locale)

      -- first select statement

    else

        call procedure with @failover

    ------------------

    Also, it is not clear - are you calling the same procedure again or you're calling different procedure?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Saeid Hasani Thursday, June 5, 2014 9:01 PM
    • Marked as answer by NickLiveTech Wednesday, July 30, 2014 3:25 PM
    Thursday, June 5, 2014 8:51 PM
  • I can't really use this solution.  While it would get the job done, it effectively doubles the amount of work the stored proc does regardless of whether the first select fails or not.  This proc is called millions of times a month, and I can't make a change that will double the amount of work it does. 

    I would not worry too much about that. Yes, you can bounce the data over a temp table, so that you only access the source table once, but then you get the overhead of the temp table instead. That extra access is an index seek and not very costly.

    An alterantive is:

    CREATE PROCEDURE get_one @name sysname, @fallback sysname AS
    SELECT TOP 1 *
    FROM   testtbl
    WHERE  name IN (@name, @fallback)
    ORDER  BY CASE WHEN name = @name THEN 1 ELSE 2 END

    However when I tested this on a fairly small table, I got a table scan which is not desirable. But may not happen with a larger table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Elvis Long Thursday, June 19, 2014 4:13 AM
    • Marked as answer by Kalman Toth Sunday, June 22, 2014 9:36 PM
    Friday, June 6, 2014 1:54 PM

All replies

  • I think this is the best you can do. You can, of course, change the query to use 

    IF EXISTS (select from myTable where ObjectCode = @ObjectCode and Alias = @Alias and Locale = @Locale)

      -- first select statement

    else

        call procedure with @failover

    ------------------

    Also, it is not clear - are you calling the same procedure again or you're calling different procedure?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Saeid Hasani Thursday, June 5, 2014 9:01 PM
    • Marked as answer by NickLiveTech Wednesday, July 30, 2014 3:25 PM
    Thursday, June 5, 2014 8:51 PM
  • It's the same procedure with the @failoverLocale being supplied to the @Locale parameter. 

    Also, the problem with doing it the way is you suggest is I would double the number of selects if it's successful the first time, quadruple the number of selects if the @failoverLocale is used.

    Thursday, June 5, 2014 8:55 PM
  • select top 1 * from (
    SELECT * from myTable
       WHERE
         ObjectCode	= @ObjectCode and
         Alias			= @Alias and
         Locale		= @Locale
    UNION ALL
    SELECT * from myTable
       WHERE
         ObjectCode	= @ObjectCode and
         Alias			= @Alias and
         Locale		= @failOverLocale)


    Thursday, June 5, 2014 8:58 PM
  • Well, you need to test what is better - your original idea or that idea. There is nothing more you can do.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 5, 2014 9:12 PM
  • select top 1 * from (
    SELECT * from myTable
       WHERE
         ObjectCode     = @ObjectCode and
         Alias               = @Alias and
         Locale          = @Locale
    UNION ALL
    SELECT * from myTable
       WHERE
         ObjectCode     = @ObjectCode and
         Alias               = @Alias and
         Locale          = @failOverLocale)

    It needs an ORDER BY:

    ORDER BY CASE Locale WHEN @Locale THEN 1 ELSE 2 END

    But apart from that, this is the solution I would use.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 5, 2014 10:06 PM
  • select top 1 * from (
    SELECT * from myTable
       WHERE
         ObjectCode     = @ObjectCode and
         Alias               = @Alias and
         Locale          = @Locale
    UNION ALL
    SELECT * from myTable
       WHERE
         ObjectCode     = @ObjectCode and
         Alias               = @Alias and
         Locale          = @failOverLocale)

    It needs an ORDER BY:

    ORDER BY CASE Locale WHEN @Locale THEN 1 ELSE 2 END

    But apart from that, this is the solution I would use.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    I can't really use this solution.  While it would get the job done, it effectively doubles the amount of work the stored proc does regardless of whether the first select fails or not.  This proc is called millions of times a month, and I can't make a change that will double the amount of work it does. 

    The best solution would be to have it do the first select, then the second only if the first returns no results.  It must do this without returning two results sets. 

    Friday, June 6, 2014 11:46 AM
  • I can't really use this solution.  While it would get the job done, it effectively doubles the amount of work the stored proc does regardless of whether the first select fails or not.  This proc is called millions of times a month, and I can't make a change that will double the amount of work it does. 

    I would not worry too much about that. Yes, you can bounce the data over a temp table, so that you only access the source table once, but then you get the overhead of the temp table instead. That extra access is an index seek and not very costly.

    An alterantive is:

    CREATE PROCEDURE get_one @name sysname, @fallback sysname AS
    SELECT TOP 1 *
    FROM   testtbl
    WHERE  name IN (@name, @fallback)
    ORDER  BY CASE WHEN name = @name THEN 1 ELSE 2 END

    However when I tested this on a fairly small table, I got a table scan which is not desirable. But may not happen with a larger table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Elvis Long Thursday, June 19, 2014 4:13 AM
    • Marked as answer by Kalman Toth Sunday, June 22, 2014 9:36 PM
    Friday, June 6, 2014 1:54 PM