SQL Server stored procedure query concatenation issues

Question

My stored procedure

    Alter PROCEDURE [dbo].[SP_AffniGetMainReports]
    @startdate datetime,
    @enddate datetime,
    @devices_count int,
    @devices_id varchar(max) 
    AS
    BEGIN 
    SET NOCOUNT ON;

     declare @affni table (id int,datetime datetime,KWH_LM float,DEVICE_ID int)

     while @startdate <=@enddate
      begin
      insert into @affni select  top (@devices_count) id ,datetime ,KWH_LM ,DEVICE_ID 
      from affni_eng_tracking where  
      datetime between  @startdate and @enddate and DEVICE_ID in (@devices_id )

     end
     select * from @affni
    end

My inputs will be

EXEC    @return_value = [dbo].[SP_AffniGetMainReports]
        @startdate = N'2019-08-01 12:00',
        @enddate = N'2019-08-06 12:59',
        @devices_id = '101,102'
GO

It gives me an empty result. If I give input as @devices_id = 101 instead of @devices_id = '101,102' it gives the results under 101. I need to get combined result for two or more result ids. And if I manually set the stored procedures query to

insert into @affni select  top (@devices_count) id ,datetime ,KWH_LM ,DEVICE_ID 
  from affni_eng_tracking where  
  datetime between  @startdate and @enddate and DEVICE_ID in (101,102)

It gives me the result associated with 101 and 102.

I have tried dynamic querying but it takes about 40sec to execute the query. This above static query return data within 1sec.

Can anyone help me out to generate query to place the @devices_id as an Integer within the query

I think Device_ID that your passing is a string value but the actual column needs integer value. so first convert the string into individual value then try.... — Ajay, Aug 07 '19 at 05:40
You can use a dynamic query in this case,but should use with sp_executesql since it can leverage cached query plans. You must be using dynamic query with 'exec' which is why you are probably getting high execution time. Here's sp_Executesql detail: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017 — Shikhar Arora, Aug 07 '19 at 05:46
I don't think the question contains the real code. `@startdate` never changes, so `while @startdate <= @enddate` causes an infinite loop. — AlwaysLearning, Aug 07 '19 at 05:52
Don't use `sp_` as a prefix to your stored procedure names. https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — gvee, Aug 07 '19 at 06:17

AlwaysLearning · Answer 1 · 2019-08-07T06:56:45.520

If you are using SQL Server 2016 or later, or more specifically, DATABASE COMPATIBILITY_LEVEL = 130 or later, you can make use of the STRING_SPLIT (Transact-SQL) function.

Given the following setup data:

create table affni_eng_tracking (
    id int not null,
    [datetime] datetime not null,
    KWH_LM float,
    DEVICE_ID int
);
insert affni_eng_tracking (id, [datetime], KWH_LM, DEVICE_ID) values
    (1, N'2019-08-02 12:00', 12.34, 100),
    (2, N'2019-08-03 12:00', 23.45, 101),
    (3, N'2019-08-04 12:00', 34.56, 102),
    (4, N'2019-08-05 12:00', 45.67, 103);

The following code will return rows for DEVICE_ID 101 and 102:

alter procedure [dbo].[SP_AffniGetMainReports]
    @startdate datetime,
    @enddate datetime,
    @devices_count int,
    @devices_id varchar(max) 
as
begin 
    set nocount on;

    select  top (@devices_count)
        id, [datetime], KWH_LM ,DEVICE_ID 
    from affni_eng_tracking
    where datetime between @startdate and @enddate
    and DEVICE_ID in (select value from string_split(@devices_id, ','))
end
go

declare @return_value int
exec    @return_value = [dbo].[SP_AffniGetMainReports]
        @startdate = N'2019-08-01 12:00',
        @enddate = N'2019-08-06 12:59',
        @devices_count = 42,
        @devices_id = '101,102'
go

| id |                datetime | KWH_LM | DEVICE_ID |
|----|-------------------------|--------|-----------|
|  2 | 2019-08-03 12:00:00.000 |  23.45 |       101 |
|  3 | 2019-08-04 12:00:00.000 |  34.56 |       102 |

The clause DEVICE_ID in (select value from string_split(@devices_id, ',')) relies on @devices_id being a list of INT values. If you give it something like @devices_id = '1,apple,3' you will get an error message such as:

Conversion failed when converting the varchar value 'apple' to data type int.

You can shield against that error by wrapping value in a try_convert() such as the following:

    and DEVICE_ID in (select try_convert(int, value) from string_split(@devices_id, ','))

Hope this helps.

I'm getting error **Invalid object name 'string_split'.** My MSSQL Version is - Microsoft SQL Server 2014 - 12.0.2269.0 (X64) — Crazy Developer, Aug 07 '19 at 06:39
You also need database compatibility level 130 or higher. What does `select compatibility_level from sys.databases where name = db_name();` return when executed in your database? — AlwaysLearning, Aug 07 '19 at 06:41
My compatibility_level is 120.. I tried change compatibility level to 130 but i got error **Valid values of the database compatibility level are 100, 110, or 120.** — Crazy Developer, Aug 07 '19 at 06:43
Sounds like you may have SQL Server 2008 and SQL Server 2014 installed side-by-side on the same computer. That error message is coming from SQL Server 2008 which has a max compatibility level of 120. — AlwaysLearning, Aug 07 '19 at 06:52
@CrazyDeveloper Argh, my apologies. Seems I'm having an off-by-10 day today. 120==SQL Server 2014; 130=SQL Server 2016. I will update my answer accordingly. REF: [ALTER DATABASE (Transact-SQL) Compatibility Level](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level) — AlwaysLearning, Aug 07 '19 at 06:55
Thanks for your support! I have used splitstring() a user defined function instead of string_split to split the string!! Now it worked ;) ref:https://stackoverflow.com/questions/10914576/t-sql-split-string — Crazy Developer, Aug 07 '19 at 19:18

score 0 · Answer 2 · answered Aug 07 '19 at 06:12

You may try this. Use string_split for getting each value on , separate basis.

Alter PROCEDURE [dbo].[SP_AffniGetMainReports]
@startdate datetime,
@enddate datetime,
@devices_count int,
@devices_id varchar(max) 
AS
BEGIN 
SET NOCOUNT ON;

 declare @affni table (id int,datetime datetime,KWH_LM float,DEVICE_ID int)

 while @startdate <=@enddate
  begin
  insert into @affni select  top (@devices_count) id ,datetime ,KWH_LM ,DEVICE_ID 
  from affni_eng_tracking where  
  datetime between  @startdate and @enddate and 
  ----   here we use string_split to get each id 
  DEVICE_ID in (select value from string_split(@devices_id,',') )  

 end
 select * from @affni
end

SQL Server stored procedure query concatenation issues

2 Answers2