case when 条件查询

Question

IF EXISTS (SELECT * FROM dbo.sysobjects 
           WHERE  id = OBJECT_ID(N'[dbo].[usp_User_SelectList]')
           and  OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[usp_User_SelectList]
GO
CREATE   PROCEDURE dbo.usp_User_SelectList
(
@UserName   varchar(50)='' ,
@FirstName  varchar(50)='' ,
@LastName   varchar(50)='' ,
@UserRoleID int        =0  ,
@AreaID     int        =0  ,
@LocationID int        =0  
)
AS 
BEGIN
        DECLARE @SQL VARCHAR(1000)
SET NOCOUNT ON

SELECT @SQL ='SELECT [User].[UserID], [User].[UserName], [User].[FirstName], [User].[LastName], [User].[Email], [User].[Phone],'      
SELECT @SQL=@SQL+' [User].[UserRoleID], [User].[NotificationTypeID], [User].[LocationID] , '
SELECT @SQL=@SQL+' UserRole.RoleName        AS   UserRoleName ,   '
SELECT @SQL=@SQL+' Location.LocationName    AS   UserLocationName ,'
SELECT @SQL=@SQL+' Area.AreaName      AS   UserAreaName     '
SELECT @SQL=@SQL+' FROM [CIF].[dbo].[User] '
SELECT @SQL=@SQL+' LEFT OUTER JOIN UserRole ON  [User].UserRoleID=UserRole.UserRoleID '
SELECT @SQL=@SQL+' LEFT OUTER JOIN Location ON  [User].LocationID=Location.LocationID '
SELECT @SQL=@SQL+' LEFT OUTER JOIN UserArea ON  [User].UserID=UserArea.UserID         '
SELECT @SQL=@SQL+' LEFT OUTER JOIN Area     ON  UserArea.AreaID=Area.AreaID         '
SELECT @SQL=@SQL+' WHERE 1>0 '
       
IF @UserName<>''
BEGIN
        SELECT @SQL=@SQL+' AND [User].UserName like '''+@UserName+''''
END

IF @FirstName<>''
BEGIN
        SELECT @SQL=@SQL+' AND [User].FirstName like '''+@FirstName+'%'''
END

IF @LastName<>''
BEGIN
        SELECT @SQL=@SQL+' AND [User].LastName='''+@LastName+''''
END

IF @UserRoleID<>0
BEGIN
        SELECT @SQL=@SQL+' AND [User].UserRoleID='+Convert(varchar(20),@UserRoleID)
END

IF @AreaID<>0
BEGIN
        SELECT @SQL=@SQL+' AND [User].AreaID='''+Convert(varchar(20),@AreaID)
END

IF @LocationID<>0
BEGIN
        SELECT @SQL=@SQL+' AND [User].LocationID='''+Convert(varchar(20),@LocationID)
END

--Print @SQL
EXEC(@SQL)

END
请问如何将条件查询 使用 WHEN CASE         WHERE (
CASE  @UserName WHEN   '' 
THEN 0
ELSE [User].[UserName] LIKE @UserName +'%'
END
 形式写出来  ？ 顺便在问一下 这样写的话是动态语句吗？是要执行存储过程时3动态编译的吗？  谢谢

Answer 1

是这样的 如果这样写的话 如果用户 在查询字段里面 输入单引号 “ ' ” 使用动态语句就会出错 ，所以我想以 where  when case 形式 来写 ，像 这样
select * from table 
where 
case when @id='' then 1=1 
else id =@id end 
order by createdate desc我写的：
SELECT  [User].[UserID], 
[User].[UserName] ,     
UserRole.RoleName        AS   UserRoleName ,   
Location.LocationName    AS   UserLocationName  
FROM [CIF].[dbo].[User] 
LEFT OUTER JOIN UserRole ON  [User].UserRoleID=UserRole.UserRoleID 
LEFT OUTER JOIN Location ON  [User].LocationID=Location.LocationID 
LEFT OUTER JOIN UserArea ON  [User].UserID=UserArea.UserID         
LEFT OUTER JOIN Area     ON  UserArea.AreaID=Area.AreaID                 WHERE 
CASE  @UserName WHEN    <> '' 
THEN [User].[UserName] like @UserName  +'%'
END
有错误 ，不知怎样解决。 谢谢大家

Answer 2

SELECT  [User].[UserID], 
[User].[UserName] ,     
UserRole.RoleName        AS   UserRoleName ,   
Location.LocationName    AS   UserLocationName  
FROM [CIF].[dbo].[User] 
LEFT OUTER JOIN UserRole ON  [User].UserRoleID=UserRole.UserRoleID 
LEFT OUTER JOIN Location ON  [User].LocationID=Location.LocationID 
LEFT OUTER JOIN UserArea ON  [User].UserID=UserArea.UserID         
LEFT OUTER JOIN Area     ON  UserArea.AreaID=Area.AreaID                 WHERE 
CASE  WHEN    @UserName<> '' 
THEN [User].[UserName] like @UserName  +'%'
END
提示 Server: Msg 156, Level 15, State 1, Procedure usp_User_SelectList, Line 54
Incorrect syntax near the keyword 'like'.

Answer 3

你把查询语句串成一个字符串在执行
WHERE里这样执行CASE 应该是不行的把
如：
declare @s_SQL varchar(8000)
set @s_SQL=''
set @s_SQL=@s_SQL+
' SELECT  [User].[UserID], 
[User].[UserName] ,     
UserRole.RoleName        AS   UserRoleName ,   
Location.LocationName    AS   UserLocationName  
FROM [CIF].[dbo].[User] 
LEFT OUTER JOIN UserRole ON  [User].UserRoleID=UserRole.UserRoleID 
LEFT OUTER JOIN Location ON  [User].LocationID=Location.LocationID 
LEFT OUTER JOIN UserArea ON  [User].UserID=UserArea.UserID         
LEFT OUTER JOIN Area     ON  UserArea.AreaID=Area.AreaID                 WHERE  '
if @UserName<> '' 
begin
set @s_SQL=@s_SQL+ ' [User].[UserName] like '+@UserName+'%'
end
else
begin
set @s_SQL=@s_SQL+' 1=1'
endexec(@s_SQL)

Answer 4

拼字符串是可以执行的  但是 如果查询条件中有 “ ‘ ”时 就会出错  所以不用动态语句 ，用case  when  可以实现，这样：
     WHERE 
CASE  WHEN    @UserName<> '' 
THEN [User].[UserName] like @UserName  +'%'
END
但是 有一些问题 
提示错误
Server: Msg 156, Level 15, State 1, Procedure usp_User_SelectList, Line 54
Incorrect syntax near the keyword 'like'.

Answer 5

case可以写在where中吗？
似乎应该写在select句子里吧

Answer 6

case 可以写在where  中

Answer 7

WHERE 
[User].[UserName] LIKE  CASE   @UserName WHEN '' THEN  '%'
 ELSE   @UserName+'%'
END 可以解决 like 条件 ， 但是 “=”条件 就没办法了

Answer 8

case when UserName like '%'+@UserName+'%' then 1 else 0
end = 1
搞定了 ，揭帖
case when UserName like '%'+@UserName+'%' then 1 else 0
end = 1