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 — Sam M
– Sam M, Commented Jun 16, 2022 at 4: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
– Sean Lange, Commented Jun 16, 2022 at 4:43
@SeanLange There is an error when I use inner Join instead of IN. — xtian
– xtian, Commented Jun 16, 2022 at 5: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
– xtian, Commented Jun 16, 2022 at 5:10

Martin Cairney · Accepted Answer · 2022-06-17 07:49:18Z

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!

Collectives™ on Stack Overflow

Simplify SQL query with string as array parameters

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related