Odd Stored Procedure behavior - 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

SQL Server Development (2000)

Odd Stored Procedure behavior

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-08-29 : 22:37:03

Hiya,
Anyone ever had this?
I'm running this stored proc:
CREATE PROCEDURE GetKeyID @Update BIT = 0
AS
DECLARE @KeyID INT
IF @Update = 1
BEGIN
UPDATE SequenceNumbers SET LastApptKey = LastApptKey + 1 FROM SequenceNumbers WITH (ROWLOCK)
SELECT @KeyID = LastApptKey FROM SequenceNumbers
END
ELSE
SELECT @KeyID = MAX(KeyId) + 1 FROM PatientAppts
--print @keyid
RETURN @KeyID

with ADO. The Keyid will be returned at random intervals only, maybe in 1 out of 15 calls, to the front end. Otherwise, the return parameter will be empty. When run in Query Analyzer, a correct result returns every time.

Sarah Berger MCSD

DavidD
Yak Posting Veteran

73 Posts

Posted - 2002-08-30 : 02:35:02

I have'nt seen that behaviour, but if the Stored Proc runs truly through QA I would be inclined to blame the properties you are using for your ADO recordset. Trying different cursortypes like dynamic or keyset may be an idea???

Regards
David

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-09-01 : 16:12:52

Am not using recordsets here. The command's parameter(0) will hold the value returned by using the RETURN statement, or the error number, in case an error occurs in the procedure. But I'm not really sure why it would come back as "Empty".

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-09-02 : 22:49:18

Seems like another ADO shenanigan. If the procedure has an input parameter, which has a default value in the sp, it would seem that you don't need to pass it to the ADO command?! But nope, ignoring that input param will make the output param/return value be empty. I can't figure out if the sp does or doesn't get called at all.

Sarah Berger MCSD

lozitskiy
Starting Member

28 Posts

Posted - 2002-09-03 : 13:50:02

Try this version:

CREATE PROCEDURE GetKeyID @Update BIT = 0
AS
DECLARE @KeyID INT
IF @Update = 1
BEGIN
UPDATE SequenceNumbers SET LastApptKey = LastApptKey + 1 FROM SequenceNumbers WITH (ROWLOCK)
SELECT @KeyID = max(LastApptKey)-- last update
FROM SequenceNumbers
END
ELSE
SELECT @KeyID = MAX(KeyId) + 1 FROM PatientAppts
--print @keyid
RETURN @KeyID

-------------
MCP MSSQL

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-09-03 : 14:08:31

Pardon my asking, but what's the point? Sequencenumbers is a one-row table. (Will always be a one-row table)

Sarah Berger MCSD

CMartin
Starting Member

13 Posts

Posted - 2002-09-04 : 19:55:25

Sarah,

Check the value of @Update and that should resolve your problem. Something like:

IF @Update IS NULL OR (@Update <> 0 AND @Update <> 1)
SET @Update = 0

I don't know if this is your case but it seems that the behavior of your SP could be due how SQL is interpreting empty strings.

From SQL BOL:
Note: Whether Microsoft® SQL Server™ interprets an empty string (NULL) as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel

Carlos

mr_mist
Grunnio

1870 Posts

Posted - 2002-09-05 : 04:53:02

Is it because it's returning results for the update?

As in, do you get better results if you use -

CREATE PROCEDURE GetKeyID @Update BIT = 0
AS
DECLARE @KeyID INT
IF @Update = 1
BEGIN
set nocount on

UPDATE SequenceNumbers SET LastApptKey = LastApptKey + 1 FROM SequenceNumbers WITH (ROWLOCK)

set nocount off
SELECT @KeyID = LastApptKey FROM SequenceNumbers
END
ELSE
SELECT @KeyID = MAX(KeyId) + 1 FROM PatientAppts
--print @keyid
RETURN @KeyID

?

Just a thought...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources