我有以下要求。当存在FirstName时,当LastName存在时,我应返回唯一的condtionFirstName;当First和email都存在时,我应返回唯一的condtionlastName;我应返回condtionFirstName + condtionEmail。
基本上无论存在哪个值,我都应返回该条件,如果1值为1,则1和2则为条件1和2,如果1和3则为条件1和3,如果只有3则为条件3。
请帮助我理解这个逻辑。
DECLARE @FirstName NVARCHAR(100)
DECLARE @LastName NVARCHAR(100)
DECLARE @Email NVARCHAR(200)
DECLARE @condtionFirstName NVARCHAR(200)
DECLARE @condtionlastName NVARCHAR(200)
DECLARE @condtionEmail NVARCHAR(200)
SET @FirstName = 'JOhn'
SET @LastName = 'David'
SET @Email = '[email protected]'
SET @condtionFirstName = ' AND FirstName = ' + '''' + @FirstName + ''''
SET @condtionlastName = ' AND LastName = ' + '''' + @LastName + ''''
SET @condtionEmail = ' AND Email = ' + '''' + @Email + ''''
这就是我长期以来所说的“厨房水槽”-您想要一个可以支持搜索条件任意组合的查询。一些想法:
sp_executesql
它们,即使它们并非全部以动态SQL语句结尾。这就像声明局部变量而不使用它。样品:
DECLARE @FirstName nvarchar(100),
@LastName nvarchar(100),
@Email nvarchar(320),
@conditions nvarchar(max) = N'';
SET @FirstName = N'John';
SET @LastName = N'David';
SET @Email = N'[email protected]';
SET @conditions += CASE WHEN @FirstName IS NOT NULL THEN
N' AND FirstName = @FirstName' ELSE N'' END
+ CASE WHEN @LastName IS NOT NULL THEN
N' AND LastName = @LastName' ELSE N'' END
+ CASE WHEN @Email IS NOT NULL THEN
N' AND Email = @Email' ELSE N'' END;
DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.table
WHERE 1 = 1' + @conditions;
PRINT @sql; -- try this when populating or not populating each of
-- @FirstName, @LastName, @Email
EXEC sys.sp_executesql @sql,
N'@FirstName nvarchar(100), @LastName nvarchar(100), @Email nvarchar(320)',
@FirstName, @LastName, @Email;
更多细节:
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句