存储过程更改where子句语句

本文关键字：子句 语句 where 存储过程 | 更新日期: 2023-09-27 18:18:15

我有一个关于sql语句的问题。可以这样查询吗?我需要过滤一个where子句，它取决于@user_id的值。

ALTER PROCEDURE [dbo].[getUserAddress]
  @user_id int = null
AS
SELECT user_id, state_code, address from UserAddress
IF(@user_id > 10)
BEGIN
 WHERE state_code = 'CA'
END
ELSE
  WHERE address = 'HILLS'
END

我试过这样做，但似乎不是一个标准的方法。

IF(@user_id > 10)
BEGIN
 SELECT user_id, state_code, address from UserAddress WHERE state_code = 'CA'
END
ELSE
   SELECT user_id, state_code, address from UserAddress WHERE address = 'HILLS'
END

请告知这个概念的最佳方法是什么。请帮助。由于

存储过程更改where子句语句

您的else后面缺少一个begin。试试这个:

IF(@user_id > 10)
BEGIN
 SELECT user_id, state_code, address from UserAddress WHERE state_code = 'CA'
END
ELSE
BEGIN
   SELECT user_id, state_code, address from UserAddress WHERE address = 'HILLS'
END

您可以简化:

select 
    user_id, 
    state_code, 
    address 
from 
    UserAddress 
where 
    (@user_id > 10 and state_code = 'CA') or
    (@user_id IS NULL OR (@user_id <= 10 and address = 'HILLS'))

如果你将来要添加更多的过滤条件，你应该意识到参数嗅探。

作为题外话，在(user_id, state_code, address)上创建索引将是这个查询的覆盖索引。

还可以在存储过程中使用动态sql查询。

查询

declare @sql as varchar(max);
select @sql = 'select * from [UserAddress] ' 
    + stuff((select case when @user_d > 10 
         then 'where state_code= ''CA''' 
         else 'where address = ''Hills''' end  
    for xml path('')), 1,0,'')
 exec(@sql);

而不是这个用法可以用在你的where语句中:

   SELECT user_id, state_code, address from UserAddress WHERE 
   (case when @user_id > 10 then state_code  else address end) 
   = (case when @user_id > 10 then 'CA' else 'HILLS' end)

试试这个

SELECT user_id, state_code, address from UserAddress
WHERE 
((WHERE state_code = 'CA') AND (@user_id > 10))
OR
((WHERE address = 'HILLS') AND (@user_id <= 10))

你可以简单地…

WHERE
    (@user_id > 10 AND state_code = 'CA')
    OR ((@user_id <= 10 OR @user_id IS NULL) AND address = 'HILLS')

然而，这种方法可能不会产生最佳的查询计划。如果结果证明这是一个问题，要么使用单独的查询(正如您已经尝试过的那样，但是按照AsheraH的建议修复语法错误)，要么使用动态SQL。

顺便说一句，你可以像这样更简洁地编写单独的查询:

IF @user_id > 10
   SELECT user_id, state_code, address from UserAddress WHERE state_code = 'CA'
ELSE
   SELECT user_id, state_code, address from UserAddress WHERE address = 'HILLS'

你可以试试:

ALTER PROCEDURE [dbo].[getUserAddress]
@user_id int = null
作为
开始

声明@sql nvarchar(5000)
set @sql='SELECT user_id, state_code, address from UserAddress'
IF(@user_id> 10)
开始
set @sql=@sql+' WHERE state_code = " CA " '
结束
其他
set @sql=@sql+' WHERE address = " HILLS " '

结束

Exec (@sql)
结束

相关文章：