SQL Server – Change Table in FROM Clause Based on UDF Parameter

functionssql server

I have two tables A and B (with exactly the same columns and overall structure) and I'm trying to create a TVF with a select inside it that chooses to use A or B based on a parameter passed to the TVF:

create function dbo.testing(@switch varchar(1))
returns @SomeData table (data int)
  begin
    insert into @SomeData
    select
      number
    from
      [[either A or B depending on the value of @switch]] <===== ???
  return
 end;

The arrow coming in from the right indicates the line I"m not sure how to handle.

What's the right way to do this? I know I can't just slap a string in there so what's the best way to proceed?

Best Answer

You don't need a multi statement TVF here.

You can use an inline one

CREATE FUNCTION dbo.testing(@switch VARCHAR(1))
RETURNS TABLE
AS
    RETURN
      (SELECT number
       FROM   A
       WHERE  @switch = 'A'
       UNION ALL
       SELECT number
       FROM   B
       WHERE  @switch = 'B')

If the value passed for @switch is a variable or parameter (rather than a constant) you may find you get better plans if you use OPTION (RECOMPILE) with this. Particularly if the cardinality of A and B is very different (likely even without the hint only the relevant one will be accessed as the table access will be under a filter with a startup expression predicate).

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – Executing a table type parameter stored procedure or function within a select clause

The way you're doing it is about the fastest, easiest and most comfortable way you're going to find to pass a TVP to a stored procedure, sorry. You can't pass expressions to stored procedure parameters, even simple things like:

EXEC sp_who2 N'act'+'ive';

Fails with:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.

In order to use an expression, it needs to be:

DECLARE @expression SYSNAME = N'act'+'ive';
EXEC sp_who2 @expression;

Best Answer

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Sql-server – Executing a table type parameter stored procedure or function within a select clause

Related Question