help w /query - set variable when no records found

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)

help w /query - set variable when no records found

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-01 : 04:08:42

Hi,

I have the following statement inside one of my SPROCS..

When the query below does not return any records, I would like the variables declared to be = 1.

Can anyone suggest the best way to do this ?

Any help much appreciated!

Thanks again

mike123

DECLARE @showMe_AddedPhoto tinyint, @showMe_AddedFriend tinyint, @showMe_ReceivedComment tinyint,

SELECT @showMe_AddedPhoto = showMe_AddedPhoto, @showMe_AddedFriend = showMe_AddedFriend FROM [dbo].[tblStatusUpdates_PrivacySettings] WITH (NOLOCK) WHERE userID = @userID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-01 : 04:13:22

[code]DECLARE @showMe_AddedPhoto tinyint,
@showMe_AddedFriend tinyint,
@showMe_ReceivedComment tinyint

SELECT @showMe_AddedPhoto = showMe_AddedPhoto,
@showMe_AddedFriend = showMe_AddedFriend
FROM dbo.tblStatusUpdates_PrivacySettings WITH (NOLOCK)
WHERE userID = @userID

IF @@ROWCOUNT = 0
SELECT @showMe_AddedPhoto = 1,
@showMe_AddedFriend = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-01 : 06:43:02

[code]
DECLARE @showMe_AddedPhoto tinyint,
@showMe_AddedFriend tinyint,
@showMe_ReceivedComment tinyint

SELECT @showMe_AddedPhoto = showMe_AddedPhoto,
@showMe_AddedFriend = showMe_AddedFriend
FROM dbo.tblStatusUpdates_PrivacySettings WITH (NOLOCK)
WHERE userID = @userID

SELECT COALESCE(@showMe_AddedPhoto, 1),
COALESCE(@showMe_AddedFriend,1)
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources