Stored Proc with XML input param to create Dynamic SQL: Is there a better way?

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  November 7, 2012 at 6:54 am

  #284533

  Hi Guys,

  I have a requirement to generate record sets which are built using a number of different filters. The filters come in different types and combinations AND the organisation has not decided who will be generating the record sets or what tool they will use to do so (options include Excel VBA, Access Form/VBA, Progress, .Net and [most likely] pass a piece of paper with the selections on to the DBA :w00t:

  Because of the complexity of the criteria and the need to make it technology agnostic I have elected to have an input parameter of type XML and use XQuery to strip it down.

  This results in building a dynamic SQL statement which is becoming increasingly complicated.

  I know that DSQL is not cachable in the Query plan chache, Is there a better way to approach this. All the examples I have found on-line have very simple XML structures and mine is a bit more complicated as different elements have different levels and the nodes need to be treated differently.

  /* XML FORMAT IS AS FOLLOWS:

  <PARAMS>1..1Always 1 instance

  <MAIN>

  <ACTION>1..1Always 1 instance

  COUNT'COUNT' or 'SELECT'

  </ACTION>

  <VERSION>1..1Always 1 instance

  CURRENTCURRENT or a date in YYYMMDD format

  </VERSION>

  <POT>1..1Always 1 instance

  ACTIVEACTIVE, CANCELLED or COMPLEX

  </POT>

  <THRESHOLD>1..1Always 1 instance

  lt150lt999 or ge999 where 999 is a numeric value

  <THRESHOLD>

  </MAIN>

  <FILTERS>1..1Always 1 instance

  <DECEASED>1..1Always 1 instance

  INCLUDEINCLUDE, EXCLUDE or ONLY

  </DECEASED>

  <VIP>1..1Always 1 instance

  EXCLUDEINCLUDE, EXCLUDE or ONLY

  </VIP>

  <CLAIMS>1..1Always 1 instance

  ONLYINCLUDE, EXCLUDE or ONLY

  </CLAIMS>

  <CRM>1..1Always 1 instance

  INCLUDE

  </CRM>

  <SPECIALNEEDS>1..1Always 1 instance: values are 'Include' and 'Only'

  <NEED>0..4If 'Include', then 0-4 choices: 0 = no includes.

  DeafIf 'Only', then 1-4 choices otherwise nothing is selected

  </NEED>NEED options are Deaf/Visually Impared/Blind Audio/Blind Braille

  <NEED>

  Braille Audio

  </NEED>

  </SPECIALNEEDS>

  <MULTIPOL>1..1Always 1 instance

  INCLUDEINCLUDE, EXCLUDE or ONLY

  </MULTIPOL>

  <MULTICASE>1..1Always 1 instance

  EXCLUDEINCLUDE, EXCLUDE or ONLY

  </MULTICASE>

  <POLICIES>0..1May be none, but never more than one

  <POLICY>0/1..0/nIf Policies exists, there must be at least one and could be many

  </POLICY>

  </POLICIES>

  </FILTERS>

  <CALLER>0..1Calling Appliation and user details are optional

  <APPLICATION>0..1

  <NAME>0..1

  </NAME>

  <VERSION>0..1

  </VERSION>

  <APPLICATION>

  <USER>0..1

  <ID>0..1

  </ID>

  <NAME>0..1

  </NAME>

  <IPADDRESS>0..1

  </IPADDRESS>

  </USER>

  </CALLER>

  </PARAMS> */

  For most of the filters, an INCLUDE means no join is necessary, an EXCLUDE means that a left join is required with a Where righthandtable is null and an ONLY means that an inner join is required.

  I end up with a DSQL statement that looks something like this

  SELECT COUNT(*) FROM

  (

  SELECT

  B.policy as 'Policy'

  FROM

  Pot B

  JOIN

  PolicyPrice PF on PF.policy = B.policy

  LEFT JOIN

  OpsBatchRecord OBR on OBR.policy= PF.policy

  AND OBR.MailStatus <> 'Cancelled'

  LEFT JOIN

  PolicyHdr F on F.policy= B.policy

  LEFT JOIN

  DECEASED DECEASED on DECEASED.householdref = F.householdref

  JOIN

  ClaimHdr CLAIMS on CLAIMS.policy= B.policy

  JOIN

  CRM CRM on CRM.policy= B.policy

  LEFT JOIN

  SpecialNeeds SPN on SPN.householdref = F.householdref

  WHERE

  B.Band = 'ACTIVE'

  AND

  PF.NetDetriment >150

  AND

  OBR.BatchID is null

  AND

  DECEASED.householdref is null

  ) X

  NOTE: The above SQL was not generated with the example values!

  The code is becoming hader to maintain and test as new criteria are added (e.g. Special needs changed from being an Inc/Exc/Only option like the others to being a four part selection as there are four different reasons for being classed as special needs.

  I can't believe I am the only person who has tried to do this, does anyone have any tips as to whether there is a better approach

• November 7, 2012 at 8:16 am

  This was removed by the editor as SPAM