I am not geting all data

Question

I am trying to get some data from my DB with a stored procedure like this

[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

My code looks like this

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();

But I get only the first select and its empty. There is data in there, and it works when I run it as a query. I am not sure where i am doing wrong. Can somebody help?

Your LIKE clause definition is wrong, see this issue: https://stackoverflow.com/questions/14237755/t-sql-and-the-where-like-parameter-clause. Use `Bruger LIKE '%' + @Bruger + '%'` instead. — Tetsuya Yamamoto, Dec 18 '18 at 06:13
Possible duplicate of [T-SQL and the WHERE LIKE %Parameter% clause](https://stackoverflow.com/questions/14237755/t-sql-and-the-where-like-parameter-clause) — Tetsuya Yamamoto, Dec 18 '18 at 06:14
Clearly it does not work when run as a query as `'%@Bruger%'` doesn't do what you think it does .Also there are a lot of reasons to not use `AddWithvalue`. Also `NCHAR` is a bad datatype for _Year_ — Nick.Mc, Dec 18 '18 at 06:32
And a discussion about not using [addwithvalue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — SMor, Dec 18 '18 at 13:29
Your procedure generates 6 resultsets of 1 (potentially) row each, not 1 resultset of 6 rows (with varying column names). Do you understand the difference? In addition, how does the consumer of this information know what each row "means"? You force the consumer to assume something based on position. This is not a good design - start over. — SMor, Dec 18 '18 at 13:36
My goal is to generate a report of income year to date based on the records the users has typed in. They will of cource newer see the code behind. The answer from Xabi gave me just what i wonted. — TheNewone, Dec 19 '18 at 04:02

score 1 · Answer 1 · answered Dec 19 '18 at 01:52

My proposal for the procedure:

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

score 0 · Answer 2 · answered Dec 18 '18 at 06:20

The LIKE clause is wrong. '%@Bruger%' Instead use '%'+ @Bruger+ '%'

Try this.

[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

score 0 · Answer 3 · answered Dec 18 '18 at 06:24

Try this:

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
;

I am not geting all data

3 Answers3