SQL Where 子句使用 if 值大于

Question

我尝试创建可选查询。我在sql proc 4中有一个参数。

ALTER PROCEDURE [dbo].[sp_OdevTümBilgiler]
@ogretmenUserId int,
@ogrenciUserId int,
@odevAdi nvarchar(50) = null,
@odevId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
s.*,
o.*,
ko.*,
ogretmenUser.UserID AS OgretmenUserId,
ogrenciUser.UserID AS OgrenciId,
(ogretmenUser.Name + ' ' + ogretmenUser.Surname) AS OgretmenFullName,
(ogrenciUser.Name + ' ' + ogrenciUser.Surname) AS OgrenciFullName
FROM KullaniciOdev AS ko
INNER JOIN Odev o
ON ko.OdevId = o.OdevId
INNER JOIN FrUser ogrenciUser
ON ogrenciUser.UserID = ko.UserId
INNER JOIN Sinif s
ON s.SinifId = o.ClassId
INNER JOIN FrUser ogretmenUser
ON ogretmenUser.UserID = s.OlusturanKullaniciId
WHERE 1=1 and (@ogrenciUserId > 0 and ko.UserId = @ogrenciUserId) and
((@ogretmenUserId > 0) and s.OlusturanKullaniciId = @ogretmenUserId) and
(@odevAdi IS NOT NULL and LEN(@odevAdi) > 0 and o.OdevAdi = @odevAdi) and
(@odevId > 0 and ko.OdevId=@odevId)
END

在我的应用程序中@ogretmenUserId默认值为 -1，

@ogrenciUserId默认值为 -1，

@odevAdi空，

@odevId -1

但是没有返回正确的值是查询为假吗？

我尝试的示例：

odevId=1 和 ogretmenUserId=1027，但没有任何返回

Answer 1

只有当您传递正确的值时，您的所有条件才会起作用。传递默认值时，它将失败。示例考虑第一个条件

(@ogrenciUserId > 0 and ko.UserId = @ogrenciUserId)

这里的默认值是-1但代码总是希望它大于0因此当传递-1时它将失败。其他条件也是如此

您需要OR条件为每个参数提供默认值。因此，何时使用默认值或以适当的值传递条件时满足条件。

WHERE  ( @ogrenciUserId = -1 
OR ko.userid = @ogrenciUserId ) 
AND ( @ogretmenUserId = -1 
OR s.olusturankullaniciid = @ogretmenUserId ) 
AND ( @odevAdi IS NULL 
OR o.odevadi = @odevAdi ) 
AND ( @odevId = -1 
OR ko.odevid = @odevId ) 

Answer 2

给定以下 WHERE 子句：

WHERE 1=1 and 
(@ogrenciUserId > 0 and ko.UserId = @ogrenciUserId) and
((@ogretmenUserId > 0) and s.OlusturanKullaniciId = @ogretmenUserId) and
(@odevAdi IS NOT NULL and LEN(@odevAdi) > 0 and o.OdevAdi = @odevAdi) and
(@odevId > 0 and ko.OdevId=@odevId)

将以下值插入

@odevId=1
@ogretmenUserId=1027
@ogrenciUserId default value is -1,
@odevAdi null,

WHERE 子句变为：

WHERE 1=1 and 
(-1 > 0 and ko.UserId = -1) and
((1027 > 0) and s.OlusturanKullaniciId = 1027) and
(null IS NOT NULL and LEN(null) > 0 and o.OdevAdi = null) and
(1 > 0 and ko.OdevId=1)

相当于

WHERE true and 
false and 
((1027 > 0) and s.OlusturanKullaniciId = 1027) and 
false and 
(1 > 0 and ko.OdevId=1)

这总是假的

由于根据输入参数，它始终为 false，因此无论您正在扫描的表行的内容如何，WHERE 子句始终返回 false，因此不会选择任何行。