CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Joe
Joe

Reputation: 379

Group the results in a stored procedure

I have a stored procedure that returns a table. One column in that table is the result of a T-SQL function. That function returns a 0 or 1 based on an ID. The stored procedure accepts IDs stored in XML.

The function is quite complex so I will omit it here, but basically the function ForeignVendor takes an ID and returns 0 or 1.

The function takes an xml argument and returns a table. I need this function as is. I am hoping to reuse it.

Here is the function:

ALTER PROCEDURE ForeignVendors
    @VendorIDs xml
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        VEN_POName, 
        T.Item.value('@VendorID[1]', 'varchar(10)') AS "VendorID", 
        ForeignVendor(T.Item.value('@VendorID[1]', 'varchar(10)')) AS "ForeignFlag"
    FROM 
        VEN V
    JOIN 
        @VendorIDs.nodes('/Root/Vendor') AS T(Item) ON V.VEN_VendorID = T.Item.value('@VendorID[1]', 'varchar(10)')
END

I can call it like this

exec [Rotair_ForeignVendors] N'<Root><Vendor VendorID="000010"/><Vendor VendorID="000011"/><Vendor VendorID="000198"/><Vendor VendorID="000021"/></Root>'

and get this back

NEMITZ TOOL CO.         000021  0
P & W CANADA HELICOPTER 000198  1
SHOP                    000011  0
ILS                     000010  0

One report need to know if any 1's exist in the last column. I was trying to pass the results to a group by query but that does not work.

Any suggestions on the most efficient way to handle this?

Upvotes: 1

Views: 2385

Answers (1)

squillman
squillman

Reputation: 13641

How about something like this?

CREATE TABLE #results
(
    Vendor VARCHAR(1000),
    VendorID varchar(20),
    ForeignFlag BIT
);

INSERT INTO #results
EXEC [Rotair_ForeignVendors] N'<Root><Vendor VendorID="000010"/><Vendor VendorID="000011"/><Vendor VendorID="000198"/><Vendor VendorID="000021"/></Root>';

SELECT COUNT(ForeignFlag)
FROM #results
WHERE ForeignFlag = 1;

DROP TABLE #results;

Upvotes: 2

Related Questions