我没有得到所有数据

Question

我正在尝试使用这样的存储过程从我的数据库中获取一些数据

[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

我的代码看起来像这样

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)) ASBEGIN&nbsp; &nbsp; SET DATEFORMAT YMD&nbsp; &nbsp; IF (@year = 2018)&nbsp; &nbsp; BEGIN&nbsp; &nbsp; &nbsp; &nbsp; WITH tm AS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT Overtid1 AS Ov1, Overtid2 AS Ov2, Vagt AS Vag, DateForQuery AS Dt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM timer&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE Bruger LIKE '%' + @Bruger + '%'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND DateForQuery BETWEEN '2018-12-01' AND '2019-11-30'&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;overtid1Before =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,overtid1After&nbsp; =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,overtid2Before =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,overtid2After&nbsp; =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,vagtBefore&nbsp; &nbsp; &nbsp;=(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,vagtAfter&nbsp; &nbsp; &nbsp; =(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')&nbsp; &nbsp; ENDEND

Answer 2

尝试这个：SELECT&nbsp;(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]&nbsp; &nbsp; @year NCHAR(10),&nbsp; &nbsp; @Bruger NCHAR(20)ASBEGIN&nbsp; &nbsp; IF (@year = 2018)&nbsp; &nbsp; BEGIN&nbsp; &nbsp; &nbsp; &nbsp; SELECT SUM(Overtid1) AS overtid1Before&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; FROM timer&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND Bruger LIKE '%'+ @Bruger +'%'&nbsp; &nbsp; &nbsp; &nbsp; SELECT SUM(Overtid1) AS overtid1after&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; FROM timer&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND Bruger LIKE '%'+ @Bruger +'%'&nbsp; &nbsp; &nbsp; &nbsp; SELECT SUM(Overtid2) AS overtid2Before&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; FROM timer&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND Bruger LIKE '%'+ @Bruger +'%'&nbsp; &nbsp; &nbsp; &nbsp; SELECT SUM(Overtid2) AS overtid2after&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; FROM timer&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND Bruger LIKE '%'+ @Bruger +'%'&nbsp; &nbsp; &nbsp; &nbsp; SELECT SUM(Vagt) AS vagtBefore&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; FROM timer&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND Bruger LIKE '%'+ @Bruger+ '%'&nbsp; &nbsp; &nbsp; &nbsp; SELECT SUM(Vagt) AS vagtafter&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; FROM timer&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND Bruger LIKE '%'+ @Bruger+ '%'&nbsp; &nbsp; ENDEND