T-sql – Procedure completes prematurely after converting comma-delimited value strings to XML

stored-procedurest-sqlxml

I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked permissions, which doesn't seem to be the issue. Then I ran the procedure through Profiler and it seemed to complete after the 'CAST AS XML…CROSS APPLY' statement, which would explain why nothing is returned. I can't figure out why this would be though. Any ideas?

Here is the bulk of the procedure, with a few table variable declarations removed

DECLARE @allprodsandpacks TABLE (
  packageID varchar(20),
  Name varchar(800),
  RN tinyint,
  productID varchar(20)
)

INSERT INTO @allprodsandpacks (packageID,Name,RN,productID)
SELECT  packageID,
        Name,
        RN,

        REPLACE(LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))),'|','') AS productID
        FROM


        (
           SELECT productID as packageID,
           ROW_NUMBER() OVER (PARTITION BY productlist ORDER BY productID) AS RN,
           Name,
           CAST('<XMLRoot><RowData>' + REPLACE(productlist, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
           FROM   TBOnline.dbo.Packages
        ) AS t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

--Profiler registers SP:Completed here!!


INSERT INTO @prods (productID)
SELECT DISTINCT productID
FROM @allprodsandpacks
WHERE RN > 1 


INSERT INTO @packs (packageID,Name)
SELECT DISTINCT 
  packageID
, Name
FROM @allprodsandpacks    
WHERE productID IN (SELECT productID FROM @prods)

SELECT 
  packageID
, Name
FROM @packs
ORDER BY Name

Best Answer

It turns out that the problem was not with the procedure itself but with the PHP function calling it. Prior to execution, I had to include

        mssql_query("SET ANSI_WARNINGS ON");
        mssql_query("SET ANSI_PADDING ON");
        mssql_query("SET CONCAT_NULL_YIELDS_NULL ON");

in the function. Setting these in the procedure itself had no effect.

My question, directed at the MS SQL Server community was misguided but I'll leave this here in case anyone else has this problem.

Related Question