Stored proc handling result set.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored proc handling result set.

Author

Topic

prasath9191
Starting Member

2 Posts

Posted - 2009-04-12 : 01:05:11

hi all,

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

Resut set for success contains :name,id,status
Result set for failure :error_id,error_desc,appl

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,
status char (1)
)

insert into @tmp

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

select @ret

select * from @tmp

If the procedure is success i m getting the value in the temp table.If it fails i m getting a error like
"Insert Error: Column name or number of supplied values does not match table definition."

Can some one pls help me to handle both the result sets.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 13:27:48

you might need an if else statement here as your resultset is differing in both cases . so it should be something like


DECLARE @tmp TABLE
(
name CHAR(70),
id INT,
status char (1) 
)

DECLARE @tmp1 TABLE
(
error_id int,
error_desc varchar(..),
appl ...
)

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

IF @ret=0
BEGIN
insert into @tmp
EXEC sptest '100','King'
END
ELSE
BEGIN
insert into @tmp1
EXEC sptest '100','King'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 13:29:05

you could also use this way if you want to avoid if ..else

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources