SQL Query - DataTable to Table Valued Parameters

Permanently deleted user

February 19, 2015 07:17

I have the following SQL query which I would like to use (it can be done through a REST query and other workflow activities but I'd prefer it were in SQL) but I need the IN part (in bold below) to be dynamic. I've read that I could dynamically create the whole WHERE clause string but that it isn't good practice i.e. it allows for SLQ injections. One of the other options is Table Valued Paramaters. Before going down this path I'm wondering if it's possible to pass a DataTable from a Workflow as a parameter and use that as the Table Valued Parameters. Has anyone done this or got an example? Or is there a better way to carry out what I'm trying to achieve?

Select

 

DISTINCT [AreaName]

 

From [NATIS1].[NATISADM].[FAUNA_TRAPPING_TRAPLOCATIONS_EVW] tl

 

WHERE (SELECT GEOMETRY::UnionAggregate(s.SHAPE)

 

FROM [NATIS1].[NATISADM].[AGM_MANAGEMENTEXTENTS_EVW] s

 

WHERE s.Application = ('Trapping') AND s.SiteName IN ('Northern Fiordland','Murchison Mountains' )).STIntersects(tl.Shape) = 1

 

ORDER BY AreaName Asc

In the example above the string values will change from ('Northern Fiordland','Murchison Mountains') to be ('Iris Burn') or ('Wellington','Iris Burn') depending on the user and therefore a variable\table coming from the Workflow. At present there wouldn't be more than 7 string values but if it can be future proofed for more strings that would be preferable.

Any help would be much appreciated. Thanks

0

• 

  Permanently deleted user

  February 26, 2015 06:39

  check it out (https://support.geocortex.com/essentialsGSCForum?sub-nav=forum&main-nav=essentials&id=9066000000008SlAAI) this thread .

   

  0
• 

  Permanently deleted user

  September 17, 2015 05:30

  Anyone got an example of how to do this using a DataTable? According to GE 4.3 release notes this was included:

  "SQL Query and SQL Non-Query now accept DataTable parameter values if the backing database is SQL Server. This can be useful when creating parameterized IN clauses."

  0
• 

  Permanently deleted user

  May 04, 2016 19:29

  Does anyone have an example of this? I have a similar problem.

  0
• 

  Permanently deleted user

  December 15, 2016 21:49

  I am also looking to do this. Would be nice if devs could give some pointers.

  0

Please sign in to leave a comment.