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