CODEWITHSUNDEEP
sqlsql-servert-sql
Sree
Sree

Reputation: 584

Case from select with null

I have lot of data and for sample purpose mention below data

    d1  date                        OL
    N   2012-03-09 00:00:00.000     NULL
    No  2011-09-26 00:00:00.000     OL
    N   2012-01-26 00:00:00.000     NOL
    N   2012-03-07 00:00:00.000     NOL
    yes 2012-02-23 00:00:00.000     NULL

i need to wrire sp like

Create proc dbo.usp_all_property_search
(@type varchar(2))
as
begin
select * from tbl where ol is
select case @type
when 'o' then null
else
not null
end

end

Plz help me .

Upvotes: 0

Views: 112

Answers (5)

user78706
user78706

Reputation: 

Create proc dbo.usp_all_property_search(@type varchar(2))
as
begin
  select * 
  from tbl 
  where 
     ( @type = 'o' and
       ol is null)        or
     ( @type != 'o' and
       ol is not null);
end

Upvotes: 0

swannee
swannee

Reputation: 3466

Does this work?

Create proc dbo.usp_all_property_search
(@type varchar(2))
as
begin
select * from tbl 
where (@type = 'o' and ol is null)
OR (@type != 'o' AND ol is not null)

end

Upvotes: 0

JNK
JNK

Reputation: 65147

You're over complicating it:

SELECT *
FROM Tbl
WHERE (@type = 'o' AND ol IS NULL)
OR (@Type <> 'o' AND ol IS NOT NULL)

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

I think you want this:

select * from tbl where (@type = 'o' and ol is null) or (@type != 'o' and ol is not null)

Case expressions have to return a value, and SQL Server doesn't even support a boolean datatype.

Upvotes: 0

amit_g
amit_g

Reputation: 31250

select * from tbl
where (@type = 'o' and ol is null) OR
    (@type <> 'o' and ol is not null)

Upvotes: 1

Related Questions