代码之家 › 专栏 › 技术社区 › Expressingx

SQL与空或非空列完全匹配

sql c#

Expressingx · 技术社区 · 7 年前

我必须根据IF行删除或插入。但其中一列可能是 NULL 所以如果我传递的值是 Col 1: 123, Col 2: 345, Col 3: NULL 此行存在,删除它插入。问题是 DBNull.Value 与空列不匹配。

查询

@"IF EXISTS(SELECT * FROM auth.UserPermissions ur WHERE ur.UserGroupId = @UserGroupId AND ur.ClientId = @ClientId AND ur.PermissionId = @PermissionId)
DELETE FROM auth.UserPermissions WHERE UserGroupId = @UserGroupId AND ClientId = @ClientId AND PermissionId = @PermissionId
ELSE
INSERT INTO auth.UserPermissions (ClientId, UserGroupId, PermissionId)
VALUES (@ClientId, @UserGroupId, @PermissionId)";

可能是 无效的 是 ClientId . 我不能用 AND (ur.ClientId = @ClientId OR ur.ClientId IS NULL) 因为如果我把两个物体 第1列:123,第2列:345,第3列:空 和 Col 1: 123, Col 2: 345, Col 3: 567 对于插入我要去插入第一个问题插入第二个是sql IF 语句将返回true并删除其中的行 客户 是 无效的 我正在经过 567 对于 客户 .

我怎样才能做到?

还有我的 反恐精英

var clientId = string.IsNullOrEmpty(_obj.ClientId) ? (object)DBNull.Value : _obj.ClientId;

string _query = @"IF EXISTS(SELECT * FROM auth.UserPermissions ur WHERE ur.UserGroupId = @UserGroupId AND ur.ClientId = @ClientId AND ur.PermissionId = @PermissionId)
DELETE FROM auth.UserPermissions WHERE UserGroupId = @UserGroupId AND ClientId = @ClientId AND PermissionId = @PermissionId
ELSE
INSERT INTO auth.UserPermissions (ClientId, UserGroupId, PermissionId)
VALUES (@ClientId, @UserGroupId, @PermissionId)";

_command.CommandText = _query;
_command.Parameters.AddWithValue("@UserGroupId", _obj.UserGroupId);
_command.Parameters.AddWithValue("@ClientId", clientId);
_command.Parameters.AddWithValue("@PermissionId", _obj.PermissionId);

await _command.ExecuteNonQueryAsync(token);
_command.Parameters.Clear();

1 回复 | 直到 7 年前

Lukasz Szozda 7 年前

你可以用 col = @param OR (col IS NULL AND @param IS NULL) :

IF EXISTS(
 SELECT 1
 FROM auth.UserPermissions ur 
 WHERE (UserGroupId=@UserGroupId OR(UserGroupId IS NULL AND @UserGroupId IS NULL))
 AND(ClientId=@ClientId OR(ClientId IS NULL AND @ClientId IS NULL))
 AND(PermissionId=@PermissionId OR(PermissionId IS NULL AND @PermissionId IS NULL))
)
DELETE FROM auth.UserPermissions 
WHERE (UserGroupId=@UserGroupId OR (UserGroupId IS NULL AND @UserGroupId IS NULL))
 AND(ClientId=@ClientId OR (ClientId IS NULL AND @ClientId IS NULL))
 AND(PermissionId=@PermissionId OR(PermissionId IS NULL AND @PermissionId IS NULL));
ELSE
INSERT INTO auth.UserPermissions (ClientId, UserGroupId, PermissionId)
VALUES (@ClientId, @UserGroupId, @PermissionId);