The statement did not return a result set

Hi I am getting the error “The statement did not return a result set” for the below queries. I have written this code in expression. I want to display @TOTALVALUE in the Label Display.

The Code is ::

DECLARE @TotalCounter INT
DECLARE @TOTALVALUE FLOAT
DECLARE @PREVTOTALVALUE FLOAT
DECLARE @CURRENTMONTH INT
SET @CURRENTMONTH=1
SET @TOTALVALUE=0
SET @PREVTOTALVALUE=0
SELECT @TotalCounter=count(pname) FROM [dbo].[sqlth_partitions]
where pname like ‘sqlt_data_1_%’ and end_time > 1622001600000
DECLARE @Counter INT
DECLARE @dbname nvarchar(100)
SET @Counter=1
WHILE ( @Counter <= @TotalCounter)
BEGIN
SELECT @dbname = pname FROM (
SELECT ROW_NUMBER() OVER(ORDER BY start_time) As rows,[pname]
FROM [dbo].[sqlth_partitions]
where pname like ‘sqlt_data_1_%’ and end_time > 1622001600000
) AS TMP
WHERE ROWS = @Counter
SET @PREVTOTALVALUE = @TOTALVALUE
Select @TOTALVALUE= total from
(SELECT SUM((CASE WHEN t.next_t_stamp IS NULL THEN (CASE WHEN @currentmonth=1 THEN DATEDIFF_BIG(ms, ‘1970-01-01 00:00:00’, GETUTCDATE()) ELSE 1627617599000 END)ELSE t.next_t_stamp END) - t.t_stamp) as total FROM (
SELECT intvalue, t_stamp,
LEAD(t_stamp) OVER(ORDER BY t_stamp) as next_t_stamp
FROM
@dbname
where tagid = 78 and t_stamp > 1627531200000 and t_stamp < 1627617599000
)t Where t.intvalue = 1
)t3
SET @TOTALVALUE = @TOTALVALUE + @PREVTOTALVALUE
SET @Counter = @Counter + 1
END

I am getting the below error.

That’s a SQL script, not a SQL statement. JDBC is only specified to run single statements (though some drivers will run scripts).

If your driver can run scripts, you would need a SELECT @TOTALVALUE before the END.

2 Likes

Thank You, It worked

I am facing another problem. I can’t able to set the dynamic Table name (@dbname). It is throwing error like below. If I can able to write the table name in Template Internal Properties, I can use this in the table name. I tried SET Function in SQL to set the value from @dbname to Template Internal Parameter which is not working.

I find the Solution. You can make the dynamic table name by achieving dynamic query.

DECLARE @Month INT
DECLARE @MonthSuffix INT
DECLARE @TotalCounter INT
DECLARE @TOTALVALUE BIGINT
DECLARE @PREVTOTALVALUE BIGINT
DECLARE @MONTHEND nvarchar(100)
DECLARE @CURRENTMONTH nvarchar(10)
DECLARE @sql nvarchar(1000)
SET @CURRENTMONTH=0
SET @TOTALVALUE=0
SET @PREVTOTALVALUE=0
SET @Month = DATEPART(MONTH, GETDATE())
SELECT @TotalCounter=count(pname) FROM [dbo].[sqlth_partitions]
where pname like ‘sqlt_data_1_%’ and end_time > 1620016973055
DECLARE @Counter INT
DECLARE @tablename nvarchar(100)
SET @tablename =’’
SET @Counter=1
WHILE ( @Counter <= @TotalCounter)
BEGIN
SELECT @MONTHEND = end_time FROM (
SELECT ROW_NUMBER() OVER(ORDER BY start_time) As rows,[end_time]
FROM [dbo].[sqlth_partitions]
where pname like ‘sqlt_data_1_%’ and end_time > 1620016973055
) AS TMP1
WHERE ROWS = @Counter
SELECT @tablename = pname FROM (
SELECT ROW_NUMBER() OVER(ORDER BY start_time) As rows,[pname]
FROM [dbo].[sqlth_partitions]
where pname like ‘sqlt_data_1_%’ and end_time > 1620016973055
) AS TMP
WHERE ROWS = @Counter
SET @MonthSuffix = Right(@tablename,2)
SET @CURRENTMONTH= CASE WHEN @Month = @MonthSuffix Then 1 Else 0 END
SET @PREVTOTALVALUE = @TOTALVALUE
set @sql = ‘Select @TOTALVALUE = isnull(total,0) from
(SELECT SUM((CASE WHEN t.next_t_stamp IS NULL THEN (CASE WHEN ’ + @currentmonth+’=1 THEN DATEDIFF_BIG(ms, ‘‘1970-01-01 00:00:00’’, GETUTCDATE()) ELSE ’ + @monthend + ’ END)ELSE t.next_t_stamp END) - t.t_stamp) as total FROM (
SELECT intvalue, t_stamp,
LEAD(t_stamp) OVER(ORDER BY t_stamp) as next_t_stamp
FROM
[dbo].[’+@tablename+’]
where tagid = 78 and t_stamp > 1620016973055
)t Where t.intvalue = 1
)t3’
Exec sp_executesql @sql , N’ @TOTALVALUE BIGINT OUTPUT’,@TOTALVALUE = @TOTALVALUE OUTPUT
SET @TOTALVALUE = @TOTALVALUE + @PREVTOTALVALUE
SET @Counter = @Counter + 1
END
SELECT @TOTALVALUE