不使用动态sql语句，正确书写case when中的null处理

 

有以下测试表

CREATE TABLE [dbo].[TableTest](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [TelNo] [varchar](50) NULL
) ON [PRIMARY]

有5条记录

insert TableTest values(1,'李一','12345')

insert TableTest values(2,'王二',Null)

insert TableTest values(3,'张三','34567')

insert TableTest values(4,'刘四',null)

insert TableTest values(5,'陈五','')

 

假设我们想写个存储过程来查询记录，条件如下

　　1.当输入参数号码为null时，查询出所有记录,(即相当于条件不起作用)

　　2.当输入参数号码为空字符时，查询出空字符和null的记录

　　3.其它，查询出以参数值开头的记录

 

我们很懒，不想用 if then写很多重复的语句，也不想使用动态sql语句来拼凑,那么该如何写该过程？

 

　　以下的写法是错误的,当传递参数为'' 或 null 时，不能查出为null的记录

Create PROCEDURE [dbo].[GetRecInfo]
(
 @pInTelNo nvarchar(30)
)
as
begin
 If @pInTelNo=''
  SET @pInTelNo = NULL;
 
 select * from TableTest
  where TelNo like(CASE WHEN @pInTelNo IS NOT NULL THEN @pInTelNo ELSE TelNo  END) + '%'
end　　

　　正确的写法应该是: 

Create PROCEDURE [dbo].[GetRecInfo]

(

　　 @pInTelNo nvarchar(30)

)

as

begin

 select * from TableTest
  where isnull(TelNo,'') LIKE (CASE
          WHEN @pInTelNo IS NULL THEN isnull(TelNo,'')
          WHEN @pInTelNo = '' THEN ''
          ELSE @pInTelNo + '%'  END)

end 

 这种方法有个缺点就是效率没有 动态sql语句 高。