Title: I am not geting all data
Tags: c#;sql-server
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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= 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?

Comment: 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.

Comment: Possible duplicate of [T-SQL and the WHERE LIKE %Parameter% clause](https://stackoverflow.com/questions/14237755/t-sql-and-the-where-like-parameter-clause)

Comment: 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_

Comment: 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.

Comment: And a discussion about not using [addwithvalue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/)

Comment: 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.


Here is another answer: 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
```


Here is another answer: 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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= CONVERT(DATETIME, '2019-05-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 
          AND Bruger LIKE '%'+ @Bruger+ '%'
    END
END
```


Here is another answer: Try this:

```SELECT 
(select sum(Overtid1) from timer where DateForQuery &gt;= 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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= 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 &gt;= Convert(datetime,'2019-05-01') and DateForQuery <= Convert(datetime,'2019-11-30') and Bruger like'%@Bruger%') as vagtafter
;
```


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 6, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2018:00:12/18/18', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<c#><sql-server>', 'Title': 'I am not geting all data', 'ViewCount': 102, 'id': 53827266, 'user_AboutMe': '<p>I live to learn</p>\n', 'user_AccountId': 7489703.0, 'user_DisplayName': 'TheNewone', 'user_Reputation': 97.0, 'user_Views': 40.0, 'user_id': 5691853.0}}