我没有得到所有数据

Question

我正在尝试使用这样的存储过程从我的数据库中获取一些数据[getAllRecordsForSalaryCalculation]    @year NCHAR(10),    @Bruger NCHAR(20)ASBEGIN    IF (@year = 2018)    BEGIN        SELECT SUM(Overtid1) AS overtid1Before         FROM timer         WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')           AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')           AND Bruger LIKE '%@Bruger%'        SELECT SUM(Overtid1) AS overtid1after         FROM timer         WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')           AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')           AND Bruger LIKE '%@Bruger%'        SELECT SUM(Overtid2) AS overtid2Before         FROM timer         WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')           AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')           AND Bruger LIKE '%@Bruger%'        SELECT SUM(Overtid2) AS overtid2after         FROM timer         WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')           AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')           AND Bruger LIKE '%@Bruger%'        SELECT SUM(Vagt) AS vagtBefore         FROM timer         WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')           AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')           AND Bruger LIKE '%@Bruger%'        SELECT SUM(Vagt) AS vagtafter         FROM timer         WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')           AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')           AND Bruger LIKE '%@Bruger%'    ENDEND我的代码看起来像这样con.Open();SqlDataAdapter da = new SqlDataAdapter("getAllRecordsForSalaryCalculation", con);da.SelectCommand.CommandType = CommandType.StoredProcedure;da.SelectCommand.Parameters.AddWithValue("@year", year);da.SelectCommand.Parameters.AddWithValue("@Bruger", bruger);da.Fill(ds);con.Close();但我只得到第一个选择并且它是空的。那里有数据，当我将它作为查询运行时它可以工作。我不确定我在哪里做错了。有人可以帮忙吗？

Answer 1

我对程序的建议：

CREATE PROC [getAllRecordsForSalaryCalculation] (@year INT, @Bruger NVARCHAR(20)) AS

BEGIN

    SET DATEFORMAT YMD

    IF (@year = 2018)

    BEGIN

        WITH tm AS (

            SELECT Overtid1 AS Ov1, Overtid2 AS Ov2, Vagt AS Vag, DateForQuery AS Dt

            FROM timer

            WHERE Bruger LIKE '%' + @Bruger + '%'

                AND DateForQuery BETWEEN '2018-12-01' AND '2019-11-30'

        )

        SELECT

             overtid1Before =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')

            ,overtid1After  =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')

            ,overtid2Before =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')

            ,overtid2After  =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')

            ,vagtBefore     =(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')

            ,vagtAfter      =(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')

    END

END

Answer 2

尝试这个：

SELECT 

(select sum(Overtid1) from timer where DateForQuery >= Convert(datetime,'2018-12-01') and DateForQuery <= Convert(datetime,'2019-04-30') and Bruger like'%@Bruger%') as overtid1Before,

(select sum(Overtid1) from timer where DateForQuery >= Convert(datetime,'2019-05-01') and DateForQuery <= Convert(datetime,'2019-11-30') and Bruger like'%@Bruger%') as overtid1after,

(select sum(Overtid2) from timer where DateForQuery >= Convert(datetime,'2018-12-01') and DateForQuery <= Convert(datetime,'2019-04-30') and Bruger like'%@Bruger%') as overtid2Before,

(select sum(Overtid2) from timer where DateForQuery >= Convert(datetime,'2019-05-01') and DateForQuery <= Convert(datetime,'2019-11-30') and Bruger like'%@Bruger%') as overtid2after,

(select sum(Vagt) from timer where DateForQuery >= Convert(datetime,'2018-12-01') and DateForQuery <= Convert(datetime,'2019-04-30') and Bruger like'%@Bruger%') as vagtBefore,

(select sum(Vagt) from timer where DateForQuery >= Convert(datetime,'2019-05-01') and DateForQuery <= Convert(datetime,'2019-11-30') and Bruger like'%@Bruger%') as vagtafter

;

Answer 3

LIKE 子句是错误的。'%@Bruger%' 而不是使用 '%'+ @Bruger+ '%'

尝试这个。

[getAllRecordsForSalaryCalculation]

    @year NCHAR(10),

    @Bruger NCHAR(20)

AS

BEGIN

    IF (@year = 2018)

    BEGIN

        SELECT SUM(Overtid1) AS overtid1Before 

        FROM timer 

        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 

          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 

          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Overtid1) AS overtid1after 

        FROM timer 

        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 

          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 

          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Overtid2) AS overtid2Before 

        FROM timer 

        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 

          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 

          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Overtid2) AS overtid2after 

        FROM timer 

        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 

          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 

          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Vagt) AS vagtBefore 

        FROM timer 

        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 

          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 

          AND Bruger LIKE '%'+ @Bruger+ '%'

        SELECT SUM(Vagt) AS vagtafter 

        FROM timer 

        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 

          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 

          AND Bruger LIKE '%'+ @Bruger+ '%'

    END

END