CASE in SP - 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)

CASE in SP

Author

Topic

jweizman
Starting Member

27 Posts

Posted - 2001-12-05 : 18:21:54

Hi

I have a problem using CASE in Stored procedure

Depending on BIDO, i do a different WHERE

This the code, but it does not work :

CREATE procedure spFL2P
@ObjectID nvarchar(20) = null,
@BIDO nvarchar(10) = null,
@DeleteFlag bit = null ,
@ReturnCode int = 0 output,
@RowCount int = 0 output
as

set nocount on

Select * from [People]
INNER JOIN A ON A.PeopleID=A.PeopleID1
Where

case

When @BIDO='P' Then [A].PeopleID1=@ObjectID
When @BIDO='A' Then [A].AddressID1=@ObjectID
When @BIDO='P' Then [A].PhotoID1=@ObjectID

end

Thanks for any help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-05 : 18:30:00

CASE returns a value. The equal sign needs to be outside of the CASE...END section. Try this:

CREATE procedure spFL2P
@ObjectID nvarchar(20) = null,
@BIDO nvarchar(10) = null,
@DeleteFlag bit = null ,
@ReturnCode int = 0 output,
@RowCount int = 0 output
as

set nocount on

Select * from [People]
INNER JOIN A ON A.PeopleID=A.PeopleID1
Where
@ObjectID=
case
When @BIDO='P' Then [A].PeopleID1
When @BIDO='A' Then [A].AddressID1
When @BIDO='P' Then [A].PhotoID1
end

jweizman
Starting Member

27 Posts

Posted - 2001-12-05 : 18:33:16

Not only you're right but i find it very beautiful to have inversded my setence :
[Crystal].PhotoID1=@objectID
to
@objectID=[Crystal].PhotoID1

Thanks very much

Jonathan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources