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('.','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