Don't understand something - 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)

Don't understand something

Author

Topic

nabeel
Starting Member

15 Posts

Posted - 2002-04-14 : 19:30:59

Hi...

I have this situation and I'm not sure what the problem is. I run a stored procedure in Query Analyzer and when it meets a certain condition I get the correct output, namely:

Target Account
----------------
1234 444555

But when it doesn't meet a certain condition, I get:

Target
--------

Target Account
----------------
4212 234323

The fact that the result is different is okay, but I don't understand why I get that empty field. In Query Analyzer it creates one window with an empty filed and then it creates another window with the results. When it meets the proper condition it just creates one window with the result set like it's supposed to.

Anybody know why it creates a separate empty field? I'm using return 1 to return the result from a query.

Thanks.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-14 : 20:20:47

If you show us some code we can help. We can not tell you what is wrong with your code without seeing it.....

Damian

nabeel
Starting Member

15 Posts

Posted - 2002-04-14 : 20:59:46

Good point... here's the code... thanks!
CREATE PROC dbo.Getresult (@key char (10), @ANI char(10))

AS

declare @areaCode char(3)
declare @prefix char(3)
declare @result char(10)
declare @custID int

select @areaCode = substring(@ANI, 1, 3)
select @prefix = substring(@ANI, 4, 3)

select @custID = custID from accounts where key = @key

/* Default result if no key found */
if @@rowcount = 0
begin
select result, '1234567890' from targets where custID = 0
return 1
end

select result from targets, prefixes where targets.custID = @custID and prefixes.custID = targets.custID and (prefixes.areaCode = @areaCode) and (prefixes.prefix = @prefix or prefixes.prefix LIKE '*')

if @@rowcount = 0
begin
select result, '1234567890' from targets where custID = 0
end

return 1

GO

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-15 : 20:05:46

Excuse me while I rant for a moment...

<rant>
C'mon people! QUIT giving yourself multiple exit points and then wondering why you can't debug your code. Repeat after me... ONE ENTRY, ONE EXIT! ONE ENTRY, ONE EXIT! ONE ENTRY, ONE EXIT!!
</rant>

OK, I feel better now. That's just a little pet peeve of mine. I don't mean to pick on you in particular, I see a lot of people do this and it drives me nuts.

Now, I believe your problem is with your first return 1 statement. BOL says that the Return statement Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

So, get rid of the Return 1 in your begin...end statement block. If you want to set a flag that you will later return at the end of the procedure, then a better way is to create a variable, set the value for that variable in your statement block, and return the variable at the end. And if you want the second SELECT to be skipped, then you'll want to use something like BEGIN SELECT statement #1 END ELSE BEGIN SELECT statement #2 END

------------------------
GENERAL-ly speaking...

nabeel
Starting Member

15 Posts

Posted - 2002-04-16 : 19:14:54

Hi...

I'm still confused about something... You mentioned multiple exit points, but what confuses me is that if it hits the first "return 1", won't it stop executing at that return statement?

I don't want it to go continue on to other statements once it reaches that one. The result was correct, it's just that first blank value that shows up, that's what I didn't get. To me it seems like it actually processes all the return statments even though the conditions don't match, is this correct?

Thanks...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-16 : 19:43:29

What I mean by multiple exit points is that your code is written so that it CAN exit the routine at multiple points along the way. In other words, you have two RETURN statements in your sproc. This leads to the potentially confusing scenario of having to guess WHICH return statement it actually used to exit your sproc. And in your example, you are using the same number (1) with your return statement, so we can't even tell by the return value which statement caused it to exit.

My preference is to have only one entry point (the top) and only one exit point (the very bottom) and set any flags along the way that I need in order to skip processing that should not be done. That way, I always know that I exited at the bottom, and if I need to track things down, I can add steps at the bottom to check auditing values just prior to exiting. It's a habit I learned eons ago and has saved me many migraines along the way. It's a matter of style, and it can probably be argued that by forcing the sproc to run all the way to the bottom I am SLIGHTLY less efficient than jumping out in the middle, I argue that the savings in maintenance headaches is well worth it.

Now, back to your problem. What version of SQL are you using? I just tested a similar scenario on my local SQL 2000, and RETURN behaved the way you would expect, and jumped out in the middle.

Can you post the calling statements that produced the results you mention in your first post? I don't see how they could have been produced by the code you posted in your second post. Also, can you return different values with your return statements (assuming that you are not ready to convert to my style) so that you can verify the system is exiting where you think it should be?

------------------------
GENERAL-ly speaking...

nabeel
Starting Member

15 Posts

Posted - 2002-04-16 : 20:42:53

Hi,

Actually, I've re-written the procedure and it seems to be working... although I am curious as to why that was happening... I'm also using SQL 2000... I was calling it using:

EXEC GetTarget '1111111111', '3105556666'

But it's not a big deal, since I do have it working... I do have a question though... what's the difference between RETURN 0, RETURN 1, etc. and are there any others (i.e. RETURN 2, RETURN 3)?

Thanks.

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-04-17 : 07:45:00

The reason being you are getting a blank row is because of the statement "select result from target,prefixes where ..." Since you are not capturing the result in a varible if rowcount is zero then you will get blank row with only one column. After that other statement will be executed i.e. "select result,'1234567890' from targets..."

It is highly recommended to have only one exit point from a sproc.

Return 1, return 2 etc is useful when you want to know from what point you have exited. That can be used in this way

declare @x int
exec @x = getresult '11111','111'
print @x

Ramesh Singh

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-17 : 14:52:07

nabeel,

You can pass back any integer with your return statement. It is up to you to capture the returned value in the calling routine and do anything with it. If you don't specify a number, but have a RETURN, it will return 0 by default.

Another way of setting a flag to determine what caused your exit would be to do something like this...

declare @returnval int
set @returnval = 0

IF ... BEGIN
  ...
  SET @returnval = 1
END ELSE BEGIN
  ...
  SET @returnval = 2
END

RETURN @returnval

Here you only have one exit point, but you know which block of code got executed and lead to the exit.

------------------------
GENERAL-ly speaking...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources