我有一个关于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
请告知这个概念的最佳方法是什么。请帮助。由于
您的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)
结束