It's going to be one of THOSE days

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)

It's going to be one of THOSE days

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-18 : 08:34:07

This is to look for existence of a record in a table

so if the record with PK @no exists in the table then the procedure returns 1, otherwise it returns 0. My problem is that it returns NULL every time!!! Is there anything obvious I might have missed?

CREATE PROCEDURE dbo.sproc_Exists
@NO varchar(10),
@VALIDNO int OUTPUT
AS

IF EXISTS (SELECT 1 FROM MyTable WHERE NO = @NO)
BEGIN -- match found
SET @VALIDNO = 1
END
ELSE
BEGIN
SET @VALIDNO = 0
END

GO

To test it I have been using this (where I know that '2002.0731' exists)

DECLARE @NUM INT
EXEC sproc_DNASampleExists '2002.0731', @NUM
SELECT @NUM -- shows results

Strange?? I'm concerned as I have a similar procedure that is much more complicated but uses the same sort of logic for existence which I haven't tested yet.

steve

A sarcasm detector, what a great idea.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-18 : 08:39:54

EXEC sproc_DNASampleExists '2002.0731', @NUM OUTPUT
?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-18 : 08:45:43

If I had TWO brain cells I'd be dangerous

Many thanks derrick

steve

A sarcasm detector, what a great idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources