CODEWITHSUNDEEP
sql-serverstored-procedurestemp-tables
Prasath
Prasath

Reputation:

result set based on success or failure

I'm having a stored procedure which returns two result sets based on the success or failure.

SP success result set: name, id ,error,desc
SP failure result sret: error,desc

I'm using the following query to get the result of the stored procedure. It returns 0 for success and -1 for failure.

declare @ret int

DECLARE @tmp TABLE (
     name  char(70),
     id    int,
     error char(2),
     desc  varchar(30)
)

insert into @tmp
EXEC @ret  = sptest '100','King'

select @ret

select * from @tmp

If the SP is success the four field gets inserted into the temp table since the column matches. But in case of failure the sp result set has only error and desc which does not matchs with no of columns in the temp table... .I can't change the Sp, so I need to do some thing (not sure) in temp table to handle both failure and success.

Upvotes: 0

Views: 1041

Answers (4)

RBarryYoung
RBarryYoung

Reputation: 56745

You cannot do this with just one call. You will have to call it once, either getting the return status and then branching depending on the status to the INSERT..EXEC command that will work for the number of columns that will be returned or Call it once, assuming success, with TRY..CATCH, and then in the Catch call it again assuming that it will fail (which is how it got to the CATCH).

Even better, would be to either re-write the stored procedure so that it returns a consistent column set or to write you own stored procedure, table-valued function or query, by extracting the code from this stored procedure and adapting it to your use. This is the proper answer in SQL.

Upvotes: 0

V'rasana Oannes
V'rasana Oannes

Reputation: 644

Try modifying your table definition so that the first two columns are nullable:

DECLARE @tmp TABLE (
 name  char(70) null,
 id    int null,
 error char(2),
 desc  varchar(30)

)

Hope this helps,

Bill

Upvotes: 0

gbn
gbn

Reputation: 432311

You can't return 2 different recordsets and load the same temp table. Neither can try and fill 2 different tables.

There are 2 options.

  1. Modify your stored proc

    • All 4 columns are returned in all conditions
    • 1st pair (name, ID) columns are NULL on error
    • 2nd pair (error, desc) are NULL on success

  2. If you are using SQL Server 2005 then use the TRY/CATCH to separate your success and fail code paths. The code below relies on using the new error handling to pass back the error result set via exception/RAISERROR.

Example:

CREATE PROC sptest
AS
DECLARE @errmsg varchar(2000)

BEGIN TRY
   do stuff
   SELECT col1, col2, col3, col4 FROM table etc
   --do more stuff
END TRY
BEGIN CATCH
   SELECT @errmsg = ERROR_MESSAGE()
   RAISERROR ('Oops! %s', 16, 1, @errmsg)
END CATCH
GO

DECLARE @tmp TABLE ( name CHAR(70), id INT, error char(2), desc varchar(30)

BEGIN TRY
    insert into @tmp
    EXEC sptest '100','King'
    select * from @tmp
END TRY
BEGIN CATCH
   PRINT ERROR_MESSAGE()
END CATCH

Upvotes: 1

Dimi Takis
Dimi Takis

Reputation: 4959

My fault!! Was too quick in the answer. You need only to relv on the return value, so building up the logic against it is much better.

If you still want to use the temp table, then calling the sptest twice could be a way to deal with it (not optimal though), one time to get the return value and based on it then have 2 different temp tables you are filling up (one would be with the 4 fields, the other only with 2 fields).

declare @ret int

DECLARE @tmp TABLE (name CHAR(70), id INT, error char(2), desc varchar(30))
DECLARE @tmperror TABLE (error char(2), desc varchar(30))


EXEC @ret = sptest '100','King'

IF @ret != 0
BEGIN
  INSERT INTO @tmperror
  EXEC sptest '100','King';
  SELECT * FROM @tmperror;
END
ELSE
BEGIN
  INSERT INTO @tmp
  EXEC sptest '100','King';
  SELECT * FROM @tmp;
END

Keep in mind that this solution is not optimal.

Upvotes: 0

Related Questions