where - 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 2008 Forums

Transact-SQL (2008)

where

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-28 : 05:33:36

Hi,
In my stored procedure, the following sql is being used.
If pass @ID = 1 then the record with ID of 1 is returned
If pass @ID = 2 then the record with ID of 2 is returned
BUT, if No @ID is passed then nothing is returned whereas I want it to return all the records.
What is wrong with the where clause please?

Thanks

@ID int = 0

select
field1, field2, field3, field4
from
tblA as a
inner join tblP as p on a.ID = p.ID
where
(p.ID = @ID and @ID > 0)
order by
p.PackageName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 05:37:52

make it


@ID int = 0

select
field1, field2, field3, field4
from
tblA as a
inner join tblP as p on a.ID = p.ID
where
(p.ID = @ID OR @ID = 0)
order by
p.PackageName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-28 : 05:42:32

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 05:46:04

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources