return true false - 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

General SQL Server Forums

New to SQL Server Programming

return true false

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-03-20 : 11:53:43

Hi,
I need to check the existence of a row in a table.
So i am using an if condition
like
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CheckNOAStages]
@NOAID int,
@StageCode nchar(20)
AS
BEGIN

SET NOCOUNT ON;
Declare @Count int
Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode
if (@Count>0)
Begin
return 1
end
else
begin
return 0
end

END

The stored proc is executing but on the Data Access Layer
I have this
Boolean exists = Convert.ToBoolean (Execute.ExecuteReader(spCollection, dbSQL));

Some how I am always getting false . How can I fix this?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 11:58:49

Add an output parameter in SP and return bit values through it.

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-03-20 : 12:04:31

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CheckNOAStages]
@NOAID int,
@StageCode nchar(20),
@Exists bit output
AS
BEGIN

SET NOCOUNT ON;
Declare @Count int
Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode
if (@Count>0)
Begin
set @Exists=true
return @Exists
end
else
begin
set @Exists=false
return @Exists
end

END

Is it the right way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 12:12:11

No need of return statements there. Also you ned to change your data access layer to receive value from this o/p variable

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-20 : 12:32:19

SET @exists = false
IF EXISTS(select * from NOAStages where NOAID=@NOAID and StageCode=@StageCode) SET @exists = true

Jim

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-03-21 : 11:50:34

As always, there are multiple ways to do this. Jim's is easier to code and for the next person looking at your sproc, simpler to understand.

Select * from NOAStages where NOAID=@NOAID and StageCode=@StageCode
SET @SelError = @@ERROR
IF @SelError <> 0 - no records returned
Begin
set @Exists=false
end
else
begin
set @Exists=true
end

Terry

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-21 : 11:59:41

It's also faster since it stops as soon as it finds a record, rather than scanning thru the whole table.

Jim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-21 : 12:13:12

In what version of SQL Server are True and False literals valid for bit data? YOu need to use 1 and 0.

Instead of an output parameter, you can also just return a single row/column result, and use ExecuteScalar() to get the answer at your client.

Just have the final line in your stored proc be:

select @Exists as [Exists]

I find that tends to be easier and results in much shorter .NET code.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources