Retruning recordset from SP - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Retruning recordset from SP

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-03-23 : 14:46:19

I have a question about returning recordset from a stored procedure.

Here's my SP:

CREATE PROCEDURE [dbo].[switch_records](
  @old_num int,
  @new_num int
) AS

declare @result int
declare @err_code int
set @result = 0

if exists (select 1 from tableA where some_num = @old_num and stat_cde = 'Y')
	if exists (select 1 from tableA where some_num = @new_num and stat_cde = 'N')
	begin
		begin tran
		update tableA set stat_cde = 'N' where some_num = @old_num and stat_cde = 'Y'
		select @err_code = @@error
		if @err_code <> 0 goto Err_H
		update tableA set stat_cde = 'Y' where some_num = @new_num and stat_cde = 'N'
		commit tran
		set @result = 1
	end
select 'result' = @result, 'error' = 0
return

Err_H:
rollback tran
select 'result' = 0, 'error' = @err_code
return

Is there something wrong with this SP? I get expected recordset when work is performed, but not when I pass invalid data, and SP breaks off in one of the IF statements at the beginning.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-23 : 14:50:00

You'll need to provide more detail on this:

quote:

but not when I pass invalid data, and SP breaks off in one of the IF statements at the beginning.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-03-23 : 15:09:21

Thank you for your answer.

I'm adding small enhancement to an existing application. The database access code is in a separate module I'd rather not recompile - which is why I'm using a recordset and not output parameter or return value.

When I call the SP with two 'correct' values - ones which do have matching records in the database, both updates are performed and I receive a recordset (via ADO), with one record and two fields with 0 in each.

But when I pass 'incorrect' values - integers for which there is no match in the database, ADO tells me that "recordset is closed".

When I execute the SP in query analyzer, I see record being returned in either case (with result=0 or 1).

Is the problem with the SP, or with the way I'm processing the results?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-23 : 15:11:02

If it is working properly in Query Analyzer, then the problem is in your code and not in the stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 15:23:23

Add SET NOCOUNT ON at the beginning of your sproc.

Jim

Everyday I learn something that somebody else already knew

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-03-24 : 09:44:49

quote:
Originally posted by jimf

Add SET NOCOUNT ON at the beginning of your sproc.

Thanks - that's what it was, if the first selects were successful, they were being included in the return. With NOCOUNT in place I don't have to keep looking for the right recordset.

Thanks again!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-24 : 10:34:13

Actually what's happening is that when the query was successful it was returning an expected result set, when the query was unsuccessful it is returning a message like "Error converting character to numeric" or some such, which breaks your front end. Plus, adding SET NO COUNT ON can really speed things up!

Jim

Everyday I learn something that somebody else already knew

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-03-24 : 12:49:16

quote:
Originally posted by jimf
Plus, adding SET NO COUNT ON can really speed things up!

thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources