My store procedure only executes my first select statement while I wrote three?

Question

This is my table:

CREATE TABLE [dbo].[InfoTable] 
(
    [Id]               INT IDENTITY (1, 1) NOT NULL,
    [TimeSpanColumn]   NVARCHAR(50) NULL,
    [TimeStampColumn]  DATETIME     NULL,
    [TimeStringColumn] NVARCHAR(50) NULL,
    [TotalSecColumn]   NVARCHAR(50) NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
)

I want to select timespans between 2 given dates and substring them to another table like this:

 Col1    Col2      col3
 [hh]  |  [mm]  |  [ss]
 ----------------------
 02       55       36
 54       32       41

My stored procedure:

CREATE PROCEDURE sumBetweenDates
    @QueryFromDate DateTime,
    @QueryToDate DateTime
AS
    SELECT 
        SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh
    FROM 
        InfoTable 
    WHERE
        TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate
    GROUP BY 
        TimeStampColumn

    SELECT 
        SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) AS mm
    FROM 
        InfoTable 
    WHERE
        TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate
    GROUP BY 
        TimeStampColumn

    SELECT 
        SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) AS ss
    FROM 
        InfoTable 
    WHERE
        TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate
    GROUP BY 
        TimeStampColumn

It only returns the result of first query. For example:

[hh]
02
54

How can I manage to do what I said?

I can't assign a variable to those select statements cause they may have more than one value after that they are grouped by timestampcolumn

Answer 1

Given your stated desired result, I think you just want a single SELECT with all 3 aggregates:

SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh,
  SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm,
  SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss
FROM InfoTable 
where TimeStampColumn between @QueryFromDate and @QueryToDate
GROUP BY TimeStampColumn

But re your comment:

I simply executed the store procedure in my c# program and filled my datagridview with the results it only showed one column.

If you have multiple SELECT statements, you have to pay extra attention to how you read them. If you're using a SqlDataReader there's the NextResult method for advancing from one result set to the next. If using some form of data adapter or similar, you need to pay attention to how it describes how to consume multiple result sets. E.g. you'd be looking for stuff to populate a DataSet, nor a DataTable.

Answer 2

If you want to calculate data between columns, then just create a temp table to store results from stored procedure and calculate the desired value.

Let me show an example.

1. Create a stored procedure

   CREATE PROCEDURE sumBetweenDates
   @QueryFromDate DateTime,
   @QueryToDate DateTime
   AS
   
   SELECT DISTINCT * FROM (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9), (10, 11, 
       12)) AS X(a, a1, a2)
   /*
   --Insert your query here instead of above test values.
   SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh,
       SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm,
       SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss
   FROM InfoTable 
   WHERE TimeStampColumn between @QueryFromDate and @QueryToDate
   GROUP BY TimeStampColumn
   */        
   

2. Create a table variable to store result of your stored procedure, Then insert data from your stored procedure sumBetweenDates '2018-12-1', '2018-12-1'. Then in SELECT statement you can do any what you want:

   Declare @T Table (col1 INT, col2 INT, col3 int)
   Insert @T Exec sumBetweenDates '2018-12-1', '2018-12-1'
   
   SELECT 
     t.col1
   , t.col2
   , t.col3 
   , t.col3 - t.col1 CalculatedColumn
   FROM @T t
   

Here is @T is a table variable. Declare @T Table (col1 INT, col2 INT, col3 int). You can know the difference between them by reading this cool article.

This answer will help to you how you can use exec stored procedure to insert data into table.

In addition, you can read an MSDN article about INSERT using EXEC statement.

Answer 3

Your stored procedure only consists of the first SELECT statement, because you are not using BEGIN/END:

CREATE PROCEDURE sumBetweenDates (
    @QueryFromDate DateTime,
    @QueryToDate DateTime
) AS
BEGIN
    SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh
    FROM InfoTable 
    WHERE TimeStampColumn between @QueryFromDate and @QueryToDate
    GROUP BY TimeStampColumn;

    SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm
    FROM InfoTable 
    WHERE TimeStampColumn BETWEEN @QueryFromDate and @QueryToDate
    GROUP BY TimeStampColumn;

    SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss
    FROM InfoTable 
    WHERE TimeStampColumn BETWEEN @QueryFromDate and @QueryToDate
    GROUP BY TimeStampColumn;
END;  -- sumBetweenDates

I am not exactly sure what you want. Based on the sample data, you seem to want:

CREATE PROCEDURE sumBetweenDates (
    @QueryFromDate DateTime,
    @QueryToDate DateTime
) AS
BEGIN
    SELECT DISTINCT CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh,
           CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm,
           CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss               
    FROM InfoTable 
    WHERE TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate;
END;

However, this doesn't seem particularly useful and one would guess that you want to sum something.