Simplify SQL query with string as array parameters

Question

I'm trying to find a way to simplify my stored procedure. When I try to query using single parameter it went well but when using array, the retrieval process took so long. Any idea how will I simplify this?

USE [TEST]
GO
/****** Object:  StoredProcedure [TESTSU].[SelectReport]    Script Date: 06/16/2022 9:29:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TESTSU].[SelectReport] (@StringAsArray [TESTSU].[StringArray] READONLY)
AS BEGIN
SELECT 
'SAM' as PLATFORM,
'SAM'+ '0'+ordh_sysrefno as ZINDEX,
ad_sapcode AS "SAP ADVERTISER CODE",
ad_advcde AS "BMS ADVERTISER CODE",
ad_advnme AS "ADVERTISER NAME",
ag_sapcode AS "SAP AGENCY CODE",
ag_agencde AS "BMS AGENCY CODE",
ag_agennme AS "AGENCY NAME",
ordh_docno AS "TO NUMBER",
ordh_createdate AS "TO CREATE DATE",
ordh_conttp AS "CONTRACT TYPE",
tt_desc AS "TELECAST TYPE",
'' AS "PACKAGE TYPE",
'' AS "REVENUE TYPE",
sapcode as "SAP PROGRAM CODE",
pg_prgcode as "BMS PROGRAM CODE",
pg_prgname as "PROGRAM",
ordd_teledte AS "TELECAST DATE",
ordd_agencost AS "INTERNAL COST",
ordd_billcost AS "BILLING COST",
'PHP' AS CURRENCY,
'' AS PRODUCTION,
spd_cpno as "CP NUMBER",
cph_cpdte as "CP DATE",
cph_prndte as "CP PRINT DATE",
CASE ordh_conttp
WHEN 'C'
       THEN spd_invno
WHEN 'X'

       THEN spd_exinvno
WHEN 'P'
       THEN spd_pbinvno
ELSE '' END AS "INVOICE NUMBER",
COALESCE(A.invh_agencom, COALESCE(B.invh_agencom,C.invh_agencom))  as "COMMISSION AMOUNT",
COALESCE(A.invh_vat,COALESCE(B.invh_vat,C.invh_vat)) as "VAT AMOUNT",
COALESCE(A.invh_billamt,COALESCE(B.invh_billamt,C.invh_billamt)) as "BILLED AMOUNT",
spd_stat as "STATUS"
from
SERVER.DB2.ADMINSA.ord_hdr INNER JOIN
SERVER.DB2.ADMINSA.ord_dtl ON (ordh_sysrefno = ordd_sysrefno) INNER JOIN
SERVER.DB2.ADMINSA.spot_dtl ON (ordd_sysrefno = spd_sysrefno and ordd_dtlno = spd_dtlno ) INNER JOIN
SERVER.DB2.ADMINSA.program ON (pg_prgcode = ordd_prgcode ) INNER JOIN
SERVER.DB2.ADMINSA.advertiser ON (ad_advcde = ordh_advcde) INNER JOIN
SERVER.DB2.ADMINSA.agency ON (ag_agencde = ordh_agencde)  INNER JOIN
SERVER.DB2.ADMINSA.cp_hdr  ON (ordh_sysrefno = cph_refno) INNER JOIN              
SERVER.DB2.ADMINSA.cp_dtl  ON (cph_cpno = cpd_cpno and ordd_teledte = cpd_teledte  and ordd_teletp = cpd_teletp and  ordd_prgcode = cpd_prgcode and ordd_pcode = cpd_pcode and  ordd_version = cpd_version and ordd_spotlen = cpd_spotlen) FULL OUTER JOIN
SERVER.DB2.ADMINSA.inv_hdr A ON (spd_invno = A.invh_invno and spd_sysrefno = A.invh_refno) FULL OUTER JOIN
SERVER.DB2.ADMINSA.inv_hdr B ON (spd_exinvno = B.invh_invno and spd_sysrefno = B.invh_refno) FULL OUTER JOIN
SERVER.DB2.ADMINSA.inv_hdr C ON (spd_exinvno = C.invh_invno and spd_sysrefno = C.invh_refno) INNER JOIN
SERVER.DB2.ADMINSA.telecast_type ON (ordd_teletp = tt_code) left outer join
SERVER.TEST.TESTSU.programs_season on (platform = 'SAM' and pg_prgcode = bmscode and
cpd_teledte BETWEEN date_start AND date_end)
WHERE
cpd_cpno in (Select LTRIM(RTRIM(StringValue)) FROM @StringAsArray)
END

Someone suggested to me to put StringAsArray into temp table but I don't know exactly what they're trying to suggest. Any help would be very much appreciated. Thank you.

Does this answer your question? [How to pass string array in SQL parameter to IN clause in SQL](https://stackoverflow.com/questions/24879020/how-to-pass-string-array-in-sql-parameter-to-in-clause-in-sql) — Sam M, Jun 16 '22 at 04:32
You should use an inner join here instead of in. Remember that your table valued parameter acts just like a normal table. — Sean Lange, Jun 16 '22 at 04:43
@SeanLange There is an error when I use inner Join instead of IN. — xtian, Jun 16 '22 at 05:08
@SamM Thanks for the suggestion could you help me with this, I'd like to create temp table for my stringasarray then JOIN it in a query. — xtian, Jun 16 '22 at 05:10
@SeanLange here's the error message: An expression of non-boolean type specified in a context where a condition is expected, near 'INNER'. this is what I did base on your suggestion cpd_cpno INNER JOIN ( Select LTRIM(RTRIM(StringValue)) FROM @StringAsArray) — xtian, Jun 16 '22 at 05:15
@StringAsArray has a user defined type ```[TESTDB].[StringArray]``` what is the defintion of the type. Also what table is the column ```cpd_cpno``` from? — Martin Cairney, Jun 16 '22 at 06:19
@MartinCairney [TESTDB].[StringArray] (TESTDB is the DB owner / schema where the stringarray was defined) cpd_cpno came from cp_dtl table you can see it on my script. — xtian, Jun 16 '22 at 06:35
The parameter ```(@StringAsArray [TESTDB].[StringArray] READONLY)``` defines @StringAsArray as type ```[TESTDB].[StringArray]``` - my question is what underlying base type is this? This will help determine what method of join would be possible. Your lack of alias' in the query also makes it difficult to identify which table any specific column comes from. Where ALL column names are unique across ALL tables this is ok, but as we don't have all table schemas it is not easy to confirm. You do not have ```cpd_cpno``` included anywhere else but you do use ```spd_cpno``` - typo or different — Martin Cairney, Jun 16 '22 at 06:49
@MartinCairney Sorry for the confusion I edited my script posted above. I am really sorry if my script is hard to understand I am just a newbie. Regarding with your question I don't really know how to answer about what underlying base type it is I'll be honest to say that I can't understand what you're trying to ask please don't be angry I just don't really know what to answer. and spd_cpno is different. Btw, DO you have idea on how will I put StringAsArray on temp table? — xtian, Jun 16 '22 at 07:06
@MartinCairney May be this will help -----------> USE [TEST] GO CREATE TYPE [TESTSU].[StringArray] AS TABLE( [StringValue] [varchar](15) NULL ) GO — xtian, Jun 16 '22 at 07:33
Why would you make a subquery like that to join? That isn't how you joined any of your other tables. You can't just stick a join in the where clause like that, it makes no sense at all. It would be joined just like any other table. Try reading some documentation or examples. — Sean Lange, Jun 16 '22 at 13:00
Two things you can do to speed up the TVP: 1. make the column in the table type a `PRIMARY KEY` 2. Don't use `LTRIM(RTRIM(`, instead do that on the client side before you pass the TVP over — Charlieface, Jun 16 '22 at 13:32

score 0 · Accepted Answer · answered Jun 17 '22 at 07:49

So with the definition of the TESTDB.StringArray as TABLE ([StringValue] varchar(15)) clarified we can look at the table joins.

Your parameter is in fact already a table. As @Charlieface mentions in the comments, I'll assume that you already have used TRIM on the values to populate the parameter before calling the Stored Procedure.

I was unable to infer which table the column sapcode came from so I have not aliased it. Likewise for the platform column in the LEFT OUTER JOIN TEST.TESTSU.programs_season condition.

ALTER PROCEDURE [TESTSU].[SelectReport] (
    @StringAsArray [TESTSU].[StringArray] READONLY
)
AS 
BEGIN
    SELECT 'SAM' as PLATFORM,
           'SAM' + '0' + ord_hdr.ordh_sysrefno as ZINDEX,
           advertiser.ad_sapcode AS "SAP ADVERTISER CODE",
           advertiser.ad_advcde AS "BMS ADVERTISER CODE",
           advertiser.ad_advnme AS "ADVERTISER NAME",
           agency.ag_sapcode AS "SAP AGENCY CODE",
           agency.ag_agencde AS "BMS AGENCY CODE",
           agency.ag_agennme AS "AGENCY NAME",
           ord_hdr.ordh_docno AS "TO NUMBER",
           ord_hdr.ordh_createdate AS "TO CREATE DATE",
           ord_hdr.ordh_conttp AS "CONTRACT TYPE",
           telecast_type.tt_desc AS "TELECAST TYPE",
           '' AS "PACKAGE TYPE",
           '' AS "REVENUE TYPE",
sapcode as "SAP PROGRAM CODE",
           program.pg_prgcode as "BMS PROGRAM CODE",
           program.pg_prgname as "PROGRAM",
           ord_dtl.ordd_teledte AS "TELECAST DATE",
           ord_dtl.ordd_agencost AS "INTERNAL COST",
           ord_dtl.ordd_billcost AS "BILLING COST",
           'PHP' AS CURRENCY,
           '' AS PRODUCTION,
           spot_dtl.spd_cpno as "CP NUMBER",
           cp_hdr.cph_cpdte as "CP DATE",
           cp_hdr.cph_prndte as "CP PRINT DATE",
           CASE ord_hdr.ordh_conttp
              WHEN 'C' THEN spot_dtl.spd_invno
              WHEN 'X' THEN spot_dtl.spd_exinvno
              WHEN 'P' THEN spot_dtl.spd_pbinvno
              ELSE '' 
           END AS "INVOICE NUMBER",
           COALESCE(A.invh_agencom, COALESCE(B.invh_agencom, C.invh_agencom))  as "COMMISSION AMOUNT",
           COALESCE(A.invh_vat, COALESCE(B.invh_vat, C.invh_vat)) as "VAT AMOUNT",
           COALESCE(A.invh_billamt, COALESCE(B.invh_billamt, C.invh_billamt)) as "BILLED AMOUNT",
           spot_dtl.spd_stat as "STATUS"
    FROM DB2.ADMINSA.ord_hdr ord_hdr
         INNER JOIN DB2.ADMINSA.ord_dtl ord_dtl 
               ON (ord_hdr.ordh_sysrefno = ord_dtl.ordd_sysrefno) 
         INNER JOIN DB2.ADMINSA.spot_dtl spot_dtl 
               ON (ord_dtl.ordd_sysrefno = spot_dtl.spd_sysrefno 
                   AND ord_dtl.ordd_dtlno = spot_dtl.spd_dtlno ) 
         INNER JOIN DB2.ADMINSA.program program 
               ON (program.pg_prgcode = ord_dtl.ordd_prgcode) 
         INNER JOIN DB2.ADMINSA.advertiser advertiser
               ON (advertiser.ad_advcde = ord_hdr.ordh_advcde) 
         INNER JOIN DB2.ADMINSA.agency agency
               ON (agency.ag_agencde = ord_hdr.ordh_agencde)  
         INNER JOIN DB2.ADMINSA.cp_hdr cp_hdr 
               ON (ord_hdr.ordh_sysrefno = cp_hdr.cph_refno) 
         INNER JOIN DB2.ADMINSA.cp_dtl cp_dtl
               ON (cph_cpno = cp_dtl.cpd_cpno 
                   AND ord_dtl.ordd_teledte = cp_dtl.cpd_teledte  
                   AND ord_dtl.ordd_teletp = cp_dtl.cpd_teletp 
                   AND ord_dtl.ordd_prgcode = cp_dtl.cpd_prgcode 
                   AND ord_dtl.ordd_pcode = cp_dtl.cpd_pcode 
                   AND ord_dtl.ordd_version = cp_dtl.cpd_version 
                   AND ord_dtl.ordd_spotlen = cp_dtl.cpd_spotlen) 
         FULL OUTER JOIN DB2.ADMINSA.inv_hdr A 
               ON (spot_dtl.spd_invno = A.invh_invno 
                   AND spot_dtl.spd_sysrefno = A.invh_refno) 
         FULL OUTER JOIN DB2.ADMINSA.inv_hdr B 
               ON (spot_dtl.spd_exinvno = B.invh_invno 
                   AND spot_dtl.spd_sysrefno = B.invh_refno) 
         FULL OUTER JOIN DB2.ADMINSA.inv_hdr C 
               ON (spot_dtl.spd_exinvno = C.invh_invno 
                   AND spot_dtl.spd_sysrefno = C.invh_refno) 
         INNER JOIN DB2.ADMINSA.telecast_type telecast_type
               ON (ord_dtl.ordd_teletp = telecast_type.tt_code) 
         LEFT OUTER JOIN TEST.TESTSU.programs_season programs_season
               ON (platform = 'SAM' 
                   AND program.pg_prgcode = programs_season.bmscode 
                   AND cp_dtl.cpd_teledte BETWEEN programs_season.date_start AND programs_season.date_end)
         INNER JOIN @StringAsArray saa
               ON cp_dtl.cpd_cpno = saa.StringValue
    WHERE
END

hello I noticed your answer super later, I have manage to solve my problem on my own but it seems that we have the same approach to solved the issue so I will mark yours as an answer. Thank you so much for taking time to resolved it and sharing your ideas it means a lot. Thank you everyone for the suggestions! — xtian, Jun 21 '22 at 02:33

Simplify SQL query with string as array parameters

1 Answers1