SP with Dynamic where condition

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)

SP with Dynamic where condition

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-10-29 : 13:40:16

Hello,
I have a Stored Procedure in which I do not want to use Dynamic SQL(Take a varchar variable and append everything and then Exec that variable).

So here is the SP ....

Create Procedure(@intCat int = 0) As
select IDNum,LastName from Users where Active = 1
go

@intCat determines the Category. If it is 0 then it means all the categories so I do not have to filter my query. If it is non zero I have to do this
select IDNum,LastName from Users where Active = 1 And CatID = @intCat

How can I do this without using Dynamic SQL?
I can do something like this ...

Create Procedure(@intCat int = 0) As
if @intCat = 0
select IDNum,LastName from Users where Active = 1
else
select IDNum,LastName from Users where Active = 1 And CatID = @intCat
go
Is there a better way?

Thanx,
maximus

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-29 : 13:45:48

Maybe not better but
select IDNum,LastName from Users where Active = 1 And (CatID = @intCat or @intCat = 0)

would usually use null and
select IDNum,LastName from Users where Active = 1 And (CatID = @intCat or @intCat is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-29 : 13:46:36

[code]Create Procedure yourproc
(@intCat int = 0)

AS

Declare @cmd varchar(250)

Select @cmd =
case
When @intCat = 0 Then 'select IDNum,LastName from Users where Active = 1'
When @intCat = 1 Then 'select IDNum,LastName from Users where Active = 1 And CatID = ' + convert(char(1),@intCat)
End

exec (@cmd)[/code]

-Jon
Should still be a "Starting Member"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 13:53:34

Well in your proc you are not using dynamic sql.
IMO it looks as good as it gets.

Alternative:
Create Procedure xx(@intCat int = 0) As
select IDNum,LastName from Users
where Active = 1
And CatID = case @intCat when 0 then CatID else @intCat end
go

If you accept null for the parameter you could do:
Create Procedure xx(@intCat int = null) As
select IDNum,LastName from Users
where Active = 1
And CatID = coalesce(@intCat,CatID)
go

rockmoose

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-29 : 13:59:53

I love case statements.

-Jon
Should still be a "Starting Member"

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-10-29 : 14:13:23

Thnx everyone for your responses.

P.S. I am using ur solution rockmoose

maximus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources