c# - 选择两个表的记录，其中任何参数都可以为空

我正在使用sql server 2008，并在c中进行开发。我刚开始一周。并坚持了这种情况。我有两张桌子
表1：

EMID    EMNA        EMCN
---------------------------
1       Junaid      1347609
2       Jhon        1347610
3       Shwan       1347611
4       ABC         1347612

表2：

EMCN        ALDT        ALTM
--------------------------------
1347609     12/21/2015  52:00.0
1347609     12/21/2015  30:00.0
1347610     12/21/2015  50:00.0
1347610     12/22/2015  30:00.0
1347611     12/23/2015  50:00.0
1347611     12/21/2015  43:00.0
1347609     12/30/2015  25:00.0
1347611     12/29/2015  13:00.0
1347611     12/28/2015  43:00.0
1347609     12/27/2015  24:00.0
1347610     12/26/2015  03:00.0

所需输出：

    EMNA    ALDT        ALTM
    -------------------------
    Junaid  30-Dec-2015 16:25
    Jhon    29-Dec-2015 20:13
    Shawn   28-Dec-2015 06:43
    Junaid  27-Dec-2015 15:24
    Jhon    26-Dec-2015 10:03
    Shawn   23-Dec-2015 11:50
    Jhon    22-Dec-2015 13:30
    Junaid  21-Dec-2015 09:30
    Jhon    21-Dec-2015 18:50
    Junaid  21-Dec-2015 14:52
    Shawn   21-Dec-2015 09:43

用户可以选择多个职员和日期范围，任何字段都可以为空，在SQL Server中此查询返回结果，但在C中无法得到结果。问题在于IN()，如果未选择任何用户，即EMID为空，则应返回所选日期范围的所有记录。如果选择一个日期，则只返回该日期的结果
SQL查询

Declare @EMID int;
Declare @FromDate date;
Declare @ToDate date;

if @FromDate is null
   SET @FromDate = @ToDate;

if @ToDate is null
   SET @ToDate = @FromDate;

Select
    E.EMNA,
    REPLACE(LEFT(CONVERT (varchar, c.ALDT, 106),11),' ','-') AS ALDT,
    CONVERT(VARCHAR(5),c.ALTM , 108) AS ALTM
from
    Table1 E
join
    Table2 C on E.EMCN = C.EMCN
where
    (E.EMID IN(@EMID) OR @EMID IS NULL)
    and (c.ALDT > @FromDate OR @FromDate IS NULL)
    and (c.ALDT < dateadd(day,1,@ToDate) OR @ToDate IS NULL)
order by
    C.ALDT DESC

SQL字符串

string strSqlQuery = string.Format(@"
Declare @EMID int;
Declare @FromDate date;
Declare @ToDate date;
SET @EMID = {0};
SET @FromDate = '{1}';

if @FromDate is null
   SET @FromDate = @ToDate;

SET @ToDate = '{2}';

if @ToDate is null
   SET @ToDate = @FromDate;

Select
    E.EMNA,
    REPLACE(LEFT(CONVERT (varchar, c.ALDT, 106),11),' ','-') AS ALDT,
    CONVERT(VARCHAR(5),c.ALTM , 108) AS ALTM
from
    A01001 E
join
    A01008 C on E.EMCN = C.EMCN
where
    (E.EMID IN(@EMID) OR @EMID IS NULL)
    and (c.ALDT > @FromDate OR @FromDate IS NULL)
    and (c.ALDT < dateadd(day, 1, @ToDate) OR @ToDate IS NULL)
order by
    Y C.ALDT DESC",
    objCabinet.strEmployeeId, strFromDate,objCabinet.strToDate);

如何修复此查询或字符串以使其在以下场景中正常工作
已选择多个员工，但未选择日期范围
选择了多个日期范围为的员工
一个日期选择多个员工
我创建了一个存储过程，但它不返回值。
存储过程：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sptEmpCabAccess]
     @EMID int = null,
     @FromDate date = null,
     @ToDate date = null
AS
BEGIN
    IF (@FromDate IS NULL)
        SET @FromDate = @ToDate;

    IF (@ToDate IS NULL)
        SET @ToDate = @FromDate;

    Select
        E.EMNA,
        REPLACE(LEFT(CONVERT (varchar, c.ALDT, 106),11),' ','-') AS ALDT,
        CONVERT(VARCHAR(5),c.ALTM , 108) AS ALTM
    from
        A01001 AS E
    join
        A01008 AS C on E.EMCN = C.EMCN
    where
        (E.EMID IN(@EMID) OR @EMID IS NULL)
        and (c.ALDT > @FromDate OR @FromDate IS NULL)
        and (c.ALDT < dateadd(day,1,@ToDate) OR @ToDate IS NULL)
    order by
        C.ALDT DESC
    END
GO

调用存储过程的C代码：

using (SqlConnection objsqlconn = new SqlConnection(conn))
{
    objsqlconn.Open();

    try
    {
        using (SqlCommand objcmd = new SqlCommand("sptEmpCabAccess", objsqlconn))
        {
            objcmd.CommandType = CommandType.StoredProcedure;

            objcmd.Parameters.Add("@EMID", SqlDbType.Int).Value = objCabinet.strEmployeeId;
            objcmd.Parameters.Add("@FromDate", SqlDbType.Date).Value = objCabinet.strFromDate; ;
            objcmd.Parameters.Add("@ToDate", SqlDbType.Date).Value = objCabinet.strToDate;

            // error here cannot implicitly convert type 'int' to 'system.data.dataset'
            dsGetData = objcmd.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

最佳答案

您需要更改应用筛选器的查询，而不是使用空值进行检查指定一些默认值，如
1）声明@emid int=0；
2）式中（e.emid in（@emid）或@emid=0）