We have a web portal we're currently building to be able to view SQL reports for our organization. At present, the portal uses a PHP script and the MS SQLSRV driver for making queries to our numerous SQL databases. Essentially, the portal works by calling stored procedures which we've created that return the requested data. The website then parses the data and presents the user with the information they requested. This process works for all of our existing reports except for one which I'm currently struggling to make work.
The problematic stored procedure/report uses a user defined table type to ultimately make a WHERE IN type clause at the end of the script in order to select against the provided account numbers.
I'm painfully aware that the SQLSRV drivers does not support sending over a user defined table type so instead I've got a working function which takes an input string in a CSV format and returns a table with the data. This portion works and I can pass it a string of account numbers and it successfully parses them out into individual rows. If I call this remotely, I get the results expected. If I provide 4 account numbers, I get my table filled with 4 rows as it should.
An example of the input string would be:
A00000000001,A00000000002,A00000000003,A00000000004
This script/stored procedure works fine if I run it manually from MS SQL Server Management Studio and feed it the same string; giving me a row for each account number provided as expected. I can also manually run the script from Mgmt Studio with the string hard coded into the script and I get the same results indicating the script works as it should. The problem though, is that when the stored procedure is executed remotely, only the last row is returned, and it's always the last account number in the string/converted table. To make matters worse, If I run the SQL Trace Profiler, I can see the requests being made, I can see that it's inserting my account numbers into my table type and doing so for every account sent over, but when the Trace Profiler shows the actual stored procedure, only the one row is returned...
I had thought the issue was in my PHP script, but seeing that the trace profiler is only capturing one row as well makes me think there is something happening with the stored procedure when run remotely...
Here's a trimmed version of the SQL without all the fields to save space:
USE [Livendb]
GO
/****** Object: StoredProcedure [dbo].[cejnsqip] Script Date: 05/25/2015 12:24:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cejnsqip] @acctList varchar(MAX)
AS
--Split our comma separated list that was sent over so we can convert it to an array
DECLARE @acctNumList acctNum_list_tbltype
INSERT INTO @acctNumList(AccountNumber) SELECT Data FROM [dbo].[cejSplitCommaList](@acctList, ',') -- Call to function to split csv into table
SELECT
...
...
...
FROM AbstractData
WHERE AbstractData.AccountNumber IN(SELECT AccountNumber FROM @acctNumList)
Not an answer, per se, but a suggestion for investigating a slightly different path...
My fallback when a TVP is not an option (typically due to driver not supporting it, as in your case) is to use an XML param instead, and then use the XML functions to parse that. Sometimes, this requires exposing the XML param as a string and then CASTing it to the XML datatype (again, depending on driver support).
This approach confers the following benefits:
- Easier to expand in the case that you need something more complex than a simple comma-separated list (say, if you wanted to send in {Acct#, Checksum} pairs)
- In my testing, extracting the interesting stuff from the XML (or XML fragment) rather than using a home-rolled parsing function puts a lighter load on the CPU
- Ability to use a schema to "strongly type" the XML (or fragment)
If it's typical that your account# list is more than ~ 50 long, you may also consider shredding whatever you use to pass the list (be it XML, or, if you figure it out, the simple string list) into a #temp table rather than a table variable, with an index on the key column. In some cases, this will help the optimizer choose a better plan.