如果sp参数具有空值，则返回所有记录 [英] Return all records if sp parameter has blank value

问题描述

我在存储过程中有一个select查询。它有一个参数@attendance。如果我传递'P'作为一个值，它会返回所有存在的emp名称，如果'A'则不存在。但如果我发送' '作为@attendance的值，我希望它返回所有存在的记录以及缺席。如何做到这一点？



目前我有以下where子句

select * from attend

I have a select query inside stored procedure.It has a parameter @attendance .If i pass 'P' as a value it returns all the emp names which are present and if 'A' then absent.but if i send '' as a value to @attendance i want it to return all the records present as well as absent.how to do this?

currently i have the following where clause
select * from attend

where Date=convert(datetime,'@Date',103) and
    Attend= case
    when @attendance is not null then @attendance 
    else
    Attend
    end

推荐答案

我希望这会对你有所帮助。

Hi,

I hope this will help you.

where Date=convert(datetime,'@Date',103) and
    Attend= case
    when @attendance <> '' then @attendance
    else
    Attend
    end

一个更简单的版本，但只有在传递NULL而不是''时才会出现。

A simpler version but wroks only when passed NULL instead of ''.

Attend=COALESCE(@attendance ,Attend)

这篇关于如果sp参数具有空值，则返回所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！