我正在做一个搜索条件表单,因为我有各种类型的搜索选项,而不是在基于控件选择的代码中执行 If 条件,我们可以通过参数值检查并更新 where 条件
Declare @Amount varchar(Max);
SET @Amount= 'and Amount=500';
Select Processor, [Stmt Date], Description, Amount, [Allocation Date],
Entity, URN, [Customer Acc], [Invoice Number],Lamount, DumpEntity as [Received Entity]
from tbl_Employee_Salary
WHERE 1=1 +CONVERT(int,CASE when @Amount IS NOT null then @Amount Else ' ' END)
将 varchar 值“and Amount=500”转换为数据类型 int 时转换失败。
警告:这是完全未经测试的。
我讨厌这些类型的查询,但你所追求的是这样的。抱歉,我不打算解释它,但是您可以自己理解以下内容并支持它。这称为全面查询或厨房水槽查询。
--All below datatypes are ASSUMED
--Declare a variable for every column (you won't need all of these if they aren't going to all be used)
DECLARE @Processor varchar(50),
@StmtDate date,
@Description varchar(100),
@Amount int,
@AllocationDate date,
@Entity int,
@URN int,
@CustomerAcc int,
@InvoiceNumber int,
@Lamount decimal(10,2),
@DumpEntity varbinary(8);
--Set your values here (I assume this will actually be an SP or something)
SET @Amount = 500;
--Create the initial SQL statement
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT Processor,' + NCHAR(13) + NCHAR(10) +
N' [Stmt Date],' + NCHAR(13) + NCHAR(10) +
N' Description,' + NCHAR(13) + NCHAR(10) +
N' Amount,' + NCHAR(13) + NCHAR(10) +
N' [Allocation Date],' + NCHAR(13) + NCHAR(10) +
N' Entity,' + NCHAR(13) + NCHAR(10) +
N' URN,' + NCHAR(13) + NCHAR(10) +
N' [Customer Acc],' + NCHAR(13) + NCHAR(10) +
N' [Invoice Number],' + NCHAR(13) + NCHAR(10) +
N' Lamount,' + NCHAR(13) + NCHAR(10) +
N' DumpEntity AS [Received Entity]' + NCHAR(13) + NCHAR(10) +
N'FROM tbl_Employee_Salary'
--Now you need to start creating the WHERE
DECLARE @Where nvarchar(MAX);
SET @Where = N'WHERE ' +
NULLIF(STUFF(CASE WHEN @Processor IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND Processor = @Processor' ELSE N'' END +
CASE WHEN @StmtDate IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND [Stmt Date] = @StmtDate' ELSE N'' END +
CASE WHEN @Description IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND [Description] = @Description' ELSE N'' END +
CASE WHEN @Amount IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND Amount = @Amount' ELSE N'' END +
CASE WHEN @AllocationDate IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND [Allocation Date] = @AllocationDate' ELSE N'' END +
CASE WHEN @Entity IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND Entity = @Entity' ELSE N'' END +
CASE WHEN @URN IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND URN = @URN' ELSE N'' END +
CASE WHEN @CustomerAcc IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND [Customer Acc] = @CustomerAcc' ELSE N'' END +
CASE WHEN @InvoiceNumber IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND [Invoice Number] = @InvoiceNumber' ELSE N'' END +
CASE WHEN @Lamount IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND Lamount = @Lamountcessor' ELSE N'' END +
CASE WHEN @DumpEntity IS NOT NULL THEN NCHAR(13) + NCHAR(10) + N' AND DumpEntity = @DumpEntity' ELSE N'' END,1,8,N''),N'');
--Now add the 2 values together
SET @SQL = @SQL + ISNULL(@Where,N'') + N';';
--Create the parameter string
DECLARE @Params nvarchar(MAX);
--All following datatypes are ASSUMED
SET @Params = N'@Processor varchar(50),@StmtDate date,@Description varchar(100),@Amount int,@AllocationDate date, @Entity int,@URN int,@CustomerAcc int,@InvoiceNumber int,@Lamount decimal(10,2),@DumpEntity varbinary(8)'
PRINT @SQL; --Your debugging best friend
--And execute the dynamic SQL
EXEC sp_executesql @SQL, @Params, @Processor, @StmtDate, @Description ,@Amount, @AllocationDate ,@Entity, @URN , @CustomerAcc, @InvoiceNumber, @Lamount, @DumpEntity;
GO
祝你好运!
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句