请指点：有2个人员表，1个是常规表，1个是归档表，给定1堆条件，怎样确定在哪个表？

[EDDGA]

如题。实际情况如下：

常规表 - [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] = '总务处'
条件组合不定

[水族杰纶]

动态传吧
把条件作为一个参数

[EDDGA]

[Quote=引用 1 楼 wufeng4552 的回复:]
动态传吧
把条件作为一个参数
[/Quote]

你好，可以详细点吗？或都可以给个例子或者关键字让我搜下google，我新手，不懂怎样动态传和把条件作参数？

[飘零一叶]

CREATE PROCEDURE USP_GETORDERS
(
    @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
END

USP_GETORDERS 10248,NULL,NULL,NULL