C＃将重复的参数传递给DataTable SQL语句

Question

我在我的DataTable SQL语句中有这个查询，现在我混淆了，如何将日期参数传递给此查询，日期只有2，startdate和enddate，但日期重复了几次倍。

SELECT   
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR 
                 shift = 'E' OR 
                 shift = 'F' OR 
                 shift = 'G' OR 
                 shift = 'H' OR 
                 shift = 'J' OR 
                 shift = 'P') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS P, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_8 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR 
                 shift = 'N' OR 
                 shift = 'O' OR 
                 shift = 'S' OR 
                 shift = 'T') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS S, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_7 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR 
                 shift = 'M' OR 
                 shift = 'Q') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS M, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_6 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'C') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS C, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_5 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'D') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS D, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_4 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'I') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS I, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_3 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'K') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS K, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_2 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'L') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS L, 
          (SELECT  COUNT(shift) AS Expr1 
           FROM   dbo.JadwalKerja AS JadwalKerja_1 
           WHERE  (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'R') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) 
         AS R 
FROM   dbo.JadwalDinas_DaftarKaryawan 
ORDER BY Nama 

Answer 1

你可以声明变量来持有这些值，因为这：

DECLARE @sDate datetime, @eDate datetime 

SET @sDate = '2015-12-01 00:00:00' 
SET @eDate = '2015-12-31 00:00:00' 

然后用@eDate

更换 CONVERT(DATETIME, '2015-12-01 00:00:00', 102) 与 @sDate 和 CONVERT(DATETIME, '2015-12-31 00:00:00', 102)您还可以创建存储过程包含所有这些查询并接收这些变量a s参数

CREATE PROCEDURE mySP @sDate datetime, @eDate datetime 
AS 
--Your query here