我正在嘗試使用這樣的存儲過程從我的數據庫中獲取一些數據[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();但我只得到第一個選擇并且它是空的。那里有數據，當我將它作為查詢運行時它可以工作。我不確定我在哪里做錯了。有人可以幫忙嗎？
3 回答
尚方寶劍之說
TA貢獻1788條經驗 獲得超4個贊
我對程序的建議：
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
翻過高山走不出你
TA貢獻1875條經驗 獲得超3個贊
嘗試這個：
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
;
www說
TA貢獻1775條經驗 獲得超8個贊
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
- 3 回答
- 0 關注
- 146 瀏覽
添加回答
舉報
0/150
提交
取消