如题。实际情况如下:常规表 - [dbo].[Person_Normal]
归档表 - [dbo].[Person_Archive]结构相同:[ID_Person] int unique not null,
[Name_Person] varchar(10) not null,
[Gender_Person] varchar(1) not null,
[Age_Person] int not null,
[Company] varchar(50),
[Department] varchar(20)
要求:各个字段由用户输入,至少一个不为空,问题:怎样因应用户的各种输入情况(就是不同的条件组合)编写where后面的条件,查询person?例如有时候是where [Name_Person] = 'xxxx' and [Age_Person] = 40,
有时候是where [Age] = 50 and [Company] = 'xx小学' and [Department] = '总务处'
条件组合不定
归档表 - [dbo].[Person_Archive]结构相同:[ID_Person] int unique not null,
[Name_Person] varchar(10) not null,
[Gender_Person] varchar(1) not null,
[Age_Person] int not null,
[Company] varchar(50),
[Department] varchar(20)
要求:各个字段由用户输入,至少一个不为空,问题:怎样因应用户的各种输入情况(就是不同的条件组合)编写where后面的条件,查询person?例如有时候是where [Name_Person] = 'xxxx' and [Age_Person] = 40,
有时候是where [Age] = 50 and [Company] = 'xx小学' and [Department] = '总务处'
条件组合不定
你好,可以详细点吗?或都可以给个例子或者关键字让我搜下google,我新手,不懂怎样动态传和把条件作参数?
(
@orderid INT =NULL,
@customerid NCHAR(5)=NULL,
@employeeid INT =NULL,
@orderdate DATETIME =NULL
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX),@PARAMS NVARCHAR(MAX)
SET @SQL=N'SELECT orderid,customerid,employeeid,orderdate'
+N' FROM Orders'
+N' WHERE 1=1'
+CASE WHEN @orderid IS NOT NULL THEN
N' AND orderid=@oid' ELSE N'' END
+CASE WHEN @customerid IS NOT NULL THEN
N' AND customerid=@custid' ELSE N'' END
+CASE WHEN @employeeid IS NOT NULL THEN
N' AND employeeid=@eid' ELSE N'' END
+CASE WHEN @orderdate IS NOT NULL THEN
N' AND orderdate=@od' ELSE N'' END
SET @PARAMS=N'@oid as int,@custid as nchar(5),@eid as int,@od as datetime'
EXEC sp_executesql @SQL,@PARAMS,@oid=@orderid,@custid=@customerid,@eid=@employeeid,@od=@orderdate
ENDUSP_GETORDERS 10248,NULL,NULL,NULL