True, false or all - 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 2005 Forums

Transact-SQL (2005)

True, false or all

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-11-10 : 05:06:09

Hi, I know how to make a sp with a bit to select on a field for only true, but I actually need something which does

@Filter = -1 - show all
@Filter = 0 - show only 'false' values in a varchar field
@Filter = 1 - show only 'true' values in a varchar field

So, should I make a 'case' statement or something for this, or can I do this smarter?

Sachin.Nand

2937 Posts

Posted - 2009-11-10 : 05:16:03

where columname =case @Filter when 0 then 'false'
when 1 then 'true'
when -1 then columname
end

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 05:18:05

select * from table where @Filter = -1 or Column = @Filter

No, you're never too old to Yak'n'Roll if you're too young to die.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-11-10 : 05:50:20

Actually it is now like this, and it says it doesn't know Member

SELECT
Distinct
Users.UserID,
Users.FirstName,
Users.LastName,
Member =
(
SELECT
UserProfile.PropertyValue
FROM
UserProfile INNER JOIN
ProfilePropertyDefinition
ON
UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID
where
ProfilePropertyDefinition.PropertyName = 'Member'
And
ProfilePropertyDefinition.portalid = @PortalID
And
UserProfile.userid = Users.UserID
)
FROM
Users
WHERE
Member =
case @Member
when 0 then 'false'
when 1 then 'true'
when -1 then Member
end
AND
(Users.IsDeleted = 0)

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-11-10 : 05:51:41

I also tried this, but it did not work either:

SELECT
Distinct
Users.UserID,
Users.FirstName,
Users.LastName,
Member =
(
SELECT
UserProfile.PropertyValue
FROM
UserProfile INNER JOIN
ProfilePropertyDefinition
ON
UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID
where
ProfilePropertyDefinition.PropertyName = 'Member'
And
UserProfile.PropertyValue =
case @Member
when 0 then 'false'
when 1 then 'true'
when -1 then UserProfile.PropertyValue
end
And
ProfilePropertyDefinition.portalid = @PortalID
And
UserProfile.userid = Users.UserID
)
FROM
Users
WHERE

(Users.IsDeleted = 0)

The secret to creativity is knowing how to hide your sources. (Einstein)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 06:13:05

[code]SELECT
Distinct
u.UserID,
u.FirstName,
u.LastName,
up.PropertyValue as Member
FROM Users u
join UserProfile up
on up.userid = u.UserID
join ProfilePropertyDefinition ppd
on ppd.PropertyDefinitionID = up.PropertyDefinitionID and
ppd.PropertyName = 'Member' and
ppd.portalid = @PortalID
where u.IsDeleted = 0
and up.PropertyValue = case
when @Member = 0 then 'false'
when @Member = 1 then 'true'
else Member
end
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources