Hi All

I'm trying to run a stored procedure but I'm having trouble doing it in C# using the Umbraco.Core.Persistence.Database class.

The following stored procedure works when I run it in SQL Server Management Studio, however when I run it C# it throws an error saying "Invalid syntax near FROM".

Here's my procedure (apologies for size):

CREATE PROCEDURE get_product_filters

@sectorId int
AS
BEGIN

SET NOCOUNT ON;
DECLARE @temp TABLE
(
    Id int,
    Name nvarchar(max),
    SortOrder int,
    Type nvarchar(max),
    ParentId int
)

INSERT INTO @temp
    SELECT c.UmbracoNodeId [Id], c.Name, categoryNode.sortOrder [SortOrder], 'Categories' [Type], NULL
    FROM Categories c
    INNER JOIN umbracoNode categoryNode ON c.UmbracoNodeId = categoryNode.id
    INNER JOIN umbracoNode catalogue ON categoryNode.parentID = catalogue.id
    INNER JOIN umbracoNode filterLanding ON catalogue.parentID = filterLanding.id
    INNER JOIN umbracoNode sector ON filterLanding.parentID = sector.id
    WHERE sector.id = @sectorId                                         
UNION ALL
    SELECT c.UmbracoNodeId [Id], c.Name, categoryNode.sortOrder [SortOrder], 'Subcategories' [Type], c.ParentCategoryId [ParentId]
    FROM Categories c
    INNER JOIN umbracoNode subCategoryNode ON c.UmbracoNodeId = subCategoryNode.id
    INNER JOIN umbracoNode categoryNode ON subCategoryNode.parentID = categoryNode.id
    INNER JOIN umbracoNode catalogue ON categoryNode.parentID = catalogue.id
    INNER JOIN umbracoNode filterLanding ON catalogue.parentID = filterLanding.id
    INNER JOIN umbracoNode sector ON filterLanding.parentID = sector.id
    WHERE sector.id = @sectorId AND c.Id IN (SELECT pc.CategoryId FROM ProductCategory pc)
UNION ALL
    SELECT region.id [Id], region.text [Name], region.sortOrder [SortOrder], 'Region' [Type], NULL
    FROM umbracoNode region
    WHERE region.id IN
        (SELECT pr.RegionId FROM Products p
        INNER JOIN ProductRegion pr ON p.Id = pr.ProductId
        INNER JOIN ProductCategory pc ON p.Id = pc.ProductId
        INNER JOIN Categories c ON pc.CategoryId = c.Id
        INNER JOIN umbracoNode parentCategoryNode ON c.ParentCategoryId = parentCategoryNode.id
        INNER JOIN umbracoNode catalogue ON parentCategoryNode.parentID = catalogue.id
        INNER JOIN umbracoNode filterLanding on catalogue.parentID = filterLanding.id
        INNER JOIN umbracoNode sector ON filterLanding.parentID = sector.id
        WHERE sector.id = @sectorId )
UNION ALL
    SELECT marketSectors.id [Id], marketSectors.text [Name], marketSectors.sortOrder [SortOrder], 'Market Sectors' [Type], NULL
    FROM umbracoNode marketSectors
    WHERE marketSectors.id IN
        (SELECT pms.SectorId FROM Products p
        INNER JOIN ProductMarketSectors pms ON p.Id = pms.ProductId
        INNER JOIN ProductCategory pc ON p.Id = pc.ProductId
        INNER JOIN Categories c ON pc.CategoryId = c.Id
        INNER JOIN umbracoNode parentCategoryNode ON c.ParentCategoryId = parentCategoryNode.id
        INNER JOIN umbracoNode catalogue ON parentCategoryNode.parentID = catalogue.id
        INNER JOIN umbracoNode filterLanding on catalogue.parentID = filterLanding.id
        INNER JOIN umbracoNode sector ON filterLanding.parentID = sector.id
        WHERE sector.id = @sectorId )
UNION ALL
    SELECT b.Id [Id], b.Name, NULL, 'Brands' [Type], NULL
    FROM Brand b
    WHERE b.Id IN
        (SELECT pb.BrandId
        FROM Products p
        INNER JOIN ProductBrand pb ON p.Id = pb.ProductId
        INNER JOIN ProductCategory pc ON p.Id = pc.ProductId
        INNER JOIN Categories c ON pc.CategoryId = c.Id
        INNER JOIN umbracoNode parentCategoryNode ON c.ParentCategoryId = parentCategoryNode.id
        INNER JOIN umbracoNode catalogue ON parentCategoryNode.parentID = catalogue.id
        INNER JOIN umbracoNode filterLanding on catalogue.parentID = filterLanding.id
        INNER JOIN umbracoNode sector ON filterLanding.parentID = sector.id
        WHERE sector.id = @sectorId )

SELECT * FROM @temp
END
GO

I've pasted the entire thing in, however I don't think there's any problems with the joins as like I said it works fine in SSMS.

Here's my C# command:

return _db.Fetch<object>(@"EXEC get_product_filters @0",
                                     sectorId);

Returning type "object" is just temporary, so I can get a list of results.

I think it may have something to do with the table variable I'm creating.

Any ideas?

Cheers