1

I have a stored procedure that is supposed to work as follows:

DECLARE @TagNames AS TABLE
(
    tag NVARCHAR(50) NOT NULL
)

-- Get list of tags
INSERT INTO @TagNames
SELECT tag
FROM tagTable
WHERE tag LIKE @tagPattern

-- Create unified result set, combining results from stored proc for each tag
SELECT n.tag, r.time, r.value FROM @TagNames AS n
CROSS APPLY
    dbo.GetResultsForTag(n.tag) AS r -- Not valid

However, I can't use CROSS APPLY because dbo.GetResultsForTag() is a stored procedure.

The implementation of dbo.GetResultsForTag() relies on calling EXEC on dynamic SQL passed to a linked server, so I believe it can't be converted into a table-valued function. Dynamic SQL isn't permitted in table-valued functions.

The linked server communicates via an OLE-DB provider, but it's not an SQL Server instance. It's an OSISoft PI process historian database. Normally it's quite easy to query for multiple tags of data in a single query, but in this case I'm doing some calculations which require me to supply a filter expression which specifies a single tag name. So each tag needs a separate query to the data source.

As far as I know, stored procedures can't be used as the right-hand side of a JOIN?

What other options are open to me for consolidating these results together? Just a cursor?

Edit: I've figured out how to fix the underlying obstacles which were forcing me to use dynamic SQL and a stored procedure. Now I can use regular SQL, which allows me to use a Table Valued Function instead of a stored proc, which allows me to use JOIN and CROSS APPLY instead of ugly cursor hacks.

Hydrargyrum
  • 3,378
  • 4
  • 27
  • 41

1 Answers1

1

Rewrite your stored proc as a Table Valued Function??

Alternatively, re-write it to take off the filter, dump it to a table (maybe a temp table), then join to that.

Milney
  • 6,253
  • 2
  • 19
  • 33
  • I don't believe I can write it as a Table Valued Function because I need to use dynamic SQL to construct the query on the remote data source. I also can't remove the tag-name filter from the remote query because the remote query isn't valid without it. – Hydrargyrum Jul 05 '17 at 08:24
  • Why don't you do the remote query on a timer, pull ALL Tags into SQL, then do the join / TVF on that data... It doesn't make sense to do a remote query every time – Milney Jul 05 '17 at 08:35
  • The GetResultsForTag stored proc asks the PI server to perform a calculation on its raw data and return the results to me. I can only do this for one tag at a time due to the design of the remote query processor - basically, I'm using a feature that requires me to pass a filter expression as a string value, and that expression also includes the tag name. I'd rather not move the (much larger) raw data over the network or reimplement the calculations at my end. – Hydrargyrum Jul 05 '17 at 08:47
  • Can you not do this in your application layer? It sounds like a terrible idea to do this at the database level.... – Milney Jul 05 '17 at 08:48
  • Not very easily - I can configure the application in various ways, but not edit its source code. It includes features for reading result sets from SQL statements or stored proc invocations, so I'm trying to write a stored proc that generates a single result set for the application to consume. – Hydrargyrum Jul 05 '17 at 23:51
  • I figured out how to fix the issues that were forcing me to use dynamic SQL, so now I can use a table-valued function instead of a stored proc. That allows me to accept your solution. – Hydrargyrum Jul 07 '17 at 01:30