SQL Server – Inspecting Anonymous Column in Stored Procedure Resultset

sql serverstored-procedures

I am writing an SQL Script using a Stored Procedure, that somebody else wrote and I cannot change, which identifies whether or not a record can be safely deleted, based on its dependents.

My problem is that this stored procedure returns an anonymous resultset in the form of

IF <Condition>
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END

Now, I think this is poor design on the part of whoever wrote this because the messages are redundant, but that is besides the point because I cannot change it and so must work with what I have got.

The problem I have is that when I go to execute this proc

IF (EXEC CanDeleteRecord @RecordID = @Record_ID)

I don't really know how I am supposed to select the first column of the result set to compare with 'Y' or 'N' because the columns are anonymous. Is there some way of enumerating the results? This is a problem I have never encountered before with SQL and Google turns up squat.

Best Answer

Store the results in a temporary table or table variable and work with the stored set.

Here's a quick prototype you can work with.

create PROCEDURE [dbo].[TestMe]
AS
BEGIN
IF 1=1
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END
END


Declare @Results Table (CanDeleteIndicator char(1),Description varchar(30))
insert @Results exec dbo.testme
select * from @Results

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 – Inserting to Temp Table and Failing

The error is happening because the stored procedure also has a #temp table or table variable and inserts into it using insert...exec. You can see it by looking at its OBJECT_DEFINITION():

insert into @countab (pendingcmdcount)  
  exec @retcode = sys.sp_MSget_repl_commands

So basically you asking for this (pseudo-code):

INSERT INTO #outer EXEC
  (SELECT * FROM (INSERT INTO #inner EXEC ... ));

This nesting of insert...exec calls is prohibited in SQL Server, for reasons that are unknown/undocumented. The typical workaround is:

SELECT * INTO #UndistributedCmds
  FROM OPENQUERY([LoopbackLinkedServer], N'EXEC procedure...');

You'll need a linked server that references the local server machine, and it will need the attribute "data access" set to true...

Best Answer

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

SQL Server – Inserting to Temp Table and Failing

Related Question