IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategoriesByPortals') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByPortals
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByPortals
	@PortalId Int
AS

SELECT
	{databaseOwner}{objectQualifier}DMX_Categories.id,
	{databaseOwner}{objectQualifier}DMX_Categories.Name,
	{databaseOwner}{objectQualifier}DMX_Categories.ParentId,
	{databaseOwner}{objectQualifier}DMX_Categories.PortalId, 
	Count(SubCats.id) AS Children
FROM 
	{databaseOwner}{objectQualifier}DMX_Categories AS SubCats RIGHT JOIN {databaseOwner}{objectQualifier}DMX_Categories ON SubCats.ParentId = {databaseOwner}{objectQualifier}DMX_Categories.id
WHERE
	{databaseOwner}{objectQualifier}DMX_Categories.PortalId = @PortalId
GROUP BY 
	{databaseOwner}{objectQualifier}DMX_Categories.id,
	{databaseOwner}{objectQualifier}DMX_Categories.Name,
	{databaseOwner}{objectQualifier}DMX_Categories.ParentId,
	{databaseOwner}{objectQualifier}DMX_Categories.PortalId

GO

