select - 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)

select

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-12-20 : 09:23:34

Hi,
Assuming you have a stored procedure as follows:
The userID may or may not be provided
how should the where clause be written to take into account the two scenarios?
I do not want to have if or case statement.
Thanks

create procedure usp_getUsers

@UserID int = null

AS

select
UserID,
UserLogin,
UserName
IsActive
from
Users
where
isnull(@UserID, UserID)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-20 : 09:26:19

[code]select
UserID,
UserLogin,
UserName
IsActive
from
Users
where
UserID = isnull(@UserID, UserID)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 09:30:28

create procedure usp_getUsers

@UserID int = null

AS

select UserID, UserLogin, UserName IsActive
from Users
where isnull(@UserID, UserID) = userid

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 09:30:46

Peter Larsson
Helsingborg, Sweden

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-12-20 : 10:15:03

Hi,
This is what I have now:
and to test it this is what I do:
UsersGet --> returns one record only whereas it shoulod return all records
UsersGet 1 --> returns one record only

p.s. there are many records in Users Table

ALTER PROCEDURE [dbo].[UsersGet]

@UserID bit = null

AS

select
UserID,
UserLogin,
UserName
IsActive
from
Users
where
UserID = isnull(@UserID, UserID)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 10:23:21

Change @UserID BIT = NULL

to

@UserID INT = NULL

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 10:49:02

quote:
Originally posted by fmardani

Hi,
This is what I have now:
and to test it this is what I do:
UsersGet --> returns one record only whereas it shoulod return all records
UsersGet 1 --> returns one record only

p.s. there are many records in Users Table

ALTER PROCEDURE [dbo].[UsersGet]

@UserID bit = null

AS

select
UserID,
UserLogin,
UserName
IsActive
from
Users
where
UserID = isnull(@UserID, UserID)

1 In your question, INT is used, why did you change it to BIT?
2 Note that all non zero values will become 1 if you assign it to BIT variable

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-20 : 12:17:19

[code]
create procedure usp_getUsers

@UserID int = null

AS

select
UserID,
UserLogin,
UserName
IsActive
from
Users
where
@UserID is null or UserID = @UserID
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources