bit field returns null - 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 2005 Forums

Transact-SQL (2005)

bit field returns null

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2007-08-28 : 06:50:57

Hi guys,

This is my simple stored procedure

ALTER PROCEDURE [dbo].[spPA_IsApplicationBlocked]
(
@ApplicationName varchar(255),
@IsBlocked bit OUTPUT
)
AS
SELECT
@IsBlocked = IsBlocked
FROM
dbo.Application
WHERE
ApplicationName = @ApplicationName

My problem is the sp returns me a null value is the Application name doesnt matches when it executes. Please help as what am i doing wrong.

Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 07:00:49

add this to the end
SELECT @IsBlocked = IsNull(@IsBlocked, 0)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 07:27:33

Well... There is a problem with that, Spirit.
If the SELECT statement do not return any records at all (ie ApplicationName doed not exists), @IsBlocked is still NULL.

ALTER PROCEDURE [dbo].[spPA_IsApplicationBlocked]
(
@ApplicationName varchar(255),
@IsBlocked bit OUTPUT
)
AS
SELECT @IsBlocked = IsBlocked
FROM dbo.Application
WHERE ApplicationName = @ApplicationName

SET @IsBlocked = ISNULL(@IsBlocked, 0)

or

ALTER PROCEDURE [dbo].[spPA_IsApplicationBlocked]
(
@ApplicationName varchar(255),
@IsBlocked bit OUTPUT
)
AS

SET @IsBlocked = 0

SELECT @IsBlocked = IsBlocked
FROM dbo.Application
WHERE ApplicationName = @ApplicationName

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 07:33:07

there is no problem with that.
it's a new statement that does exactly that.

first it's his select where if it returns nothing @IsBlocked remains null
the second (mine) select takes care of that.
No?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 07:38:41

Ahhh! Now I see! I thought you replace the SELECT statement from the table itself.

Sorry!

E 12°55'05.25"
N 56°04'39.16"

ankur_gurha
Starting Member

20 Posts

Posted - 2007-08-28 : 07:49:11

Thanks guys, help is much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources