Stored Procedure SQL 2000 to SQL 2005

Question

Post reply

Stored Procedure SQL 2000 to SQL 2005

river1

SSC-Insane

Points: 24309
More actions
September 30, 2009 at 4:03 am

#135449

Hi,
I have this stored procedure:
CREATE PROCEDURE dbo.GetObsliquidacao
@num_liq varchar(50),
@ObsBloco varchar(50)=null out,
@Obsarea varchar(50)=null out,
@Obs varchar(50)=null out
WITH ENCRYPTION
AS
declare @a int
exec @a = dbo.IsDliInObsliquidacao @num_liq
if (@a<>0)
begin
SELECT @ObsBloco= ObsBloco, @Obsarea = Obsarea, @Obs = Obs FROM OBSLIQUIDACAO WHERE codobsliq = @a
end
select @ObsBloco as ObsBloco, @Obsarea as Obsarea, @Obs as Obs
as you can see , this stored procedure, call another stored proc inside.
The code, of the called stored procedure is:
CREATE PROCEDURE dbo.IsDliInObsliquidacao @num_liq nvarchar(50)
WITH ENCRYPTION
AS
return (SELECT codobsliq FROM OBSLIQUIDACAO where NUMLIQ=@num_liq)
When the table don't have any records to return, in SQL Server 2000 the result of the stored proc is Null,Null,Null and that is correct.
In SQL Server 2005, when i execute the same stored procedure the result returned is Null,Null.NULL but it raises the message:
The 'IsDliInObsliquidacao' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
(1 row(s) affected)
This causes problems in my vb app, so i had to change the code of the called procedure to:
alter PROCEDURE dbo.IsDliInObsliquidacao @num_liq nvarchar(50)
WITH ENCRYPTION
AS
if (SELECT count(codobsliq) FROM OBSLIQUIDACAO where NUMLIQ=@num_liq) =0
begin
return 0
end
else
return (SELECT codobsliq FROM OBSLIQUIDACAO where NUMLIQ=@num_liq)
Can someone explain me, why the procedure works correctly in SQL Server 2000 and not in SQL server 2005?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Is any of your stored procedure making use of NOCOUNT ON?

MJ

river1 SSC-Insane Points: 24309 More actions · Answer 2

river1

SSC-Insane

Points: 24309

September 30, 2009 at 9:09 am

#1060013

No

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

You relied on using a feature that you are not supposed to use. RETURN is not for output parameters or values, which is what you used it for. Thus when microsoft changed the functionality your code broke.

Also, it was suboptimal to use that design in the first place. You should have directly made the table hit in the main sproc. 🙂 Failing that you should use an output parameter in the subordinate sproc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

Kevin,

I'll admit to being a bit lazy here... do you have a link about that change? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Jeff Moden (10/1/2009)
Kevin,
I'll admit to being a bit lazy here... do you have a link about that change? Thanks.

No I do not - I just assumed there was a change since the user is getting two different outcomes on different versions.

2005 BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1d9c8247-fd89-4544-be9c-01c95b745db0.htm. I note here that 2000 BOL states the same thing about NULL value getting converted to 0 and a warning message being raised. Perhaps the message severity is different and/or for some other reason the calling mechanism used by OP handles/ignores said warning??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service