June 30, 2010 at 12:15 am
CREATE PROCEDURE [dbo].[Forums.Categories.SelectAllWithInfo]
@ForumId Int = 1
,@LanguageId NChar(2) = NULL
,@CultureId NVarChar(16) =NULL
AS
BEGIN
SET NOCOUNT ON;
IF (@CultureId = N'ALL' ) SET @CultureId = NULL
DECLARE @CategoryId Int = (SELECT CategoryId FROM dbo.[Forums.Categories] WHERE ForumId = @ForumId AND ParentCategoryId IS NULL); --12;
WITH Hierarchy(DomainId, ForumId, ParentCategoryId, CategoryId, level, Name, Sort, OrderInt) AS
(
SELECT DomainId, ForumId, ParentCategoryId, CategoryId, 0, Name, CAST(Name AS NVarChar(256)), OrderInt
FROM dbo.[Forums.Categories]
WHERE CategoryId=@CategoryId
UNION ALL
SELECT FC.DomainId, FC.ForumId, FC.ParentCategoryId, FC.CategoryId, level+1, FC.Name , CAST(Sort + '|' + ISNULL(FC.OrderInt,'0') + FC.Name AS NVarChar(256)), FC.OrderInt
FROM dbo.[Forums.Categories] AS FC INNER JOIN hierarchy H ON FC.ParentCategoryId = H.CategoryId
)
,HierarchyReverse(DomainId, ForumId, ParentCategoryId, CategoryId, level, Name, Sort, OrderInt) AS
(
SELECT DomainId, ForumId, ParentCategoryId, CategoryId, 0, Name, CAST(Name AS NVarChar(256)), OrderInt
FROM Hierarchy
WHERE ParentCategoryId IS NULL
UNION ALL
SELECT H.DomainId, H.ForumId, H.ParentCategoryId, H.CategoryId,HR.level+1, H.Name , CAST(HR.Sort + '|' + H.Sort AS NVarChar(256)), H.OrderInt
FROM Hierarchy AS H INNER JOIN HierarchyReverse HR ON H.ParentCategoryId = HR.CategoryId
)
SELECT FC.DomainId, FC.ForumId, FC.CategoryId, Fc.ParentCategoryId, CAST(ISNULL(FCL.Name,FC.Name) AS NVARCHAR(1024)) AS Name, FC.Sort
,FCL.Description,FC.level ,COUNT(FC1.CategoryId) AS CategoryChilds
, COUNT(DISTINCT FCTL.ThreadId) AS Threads
,(COUNT(DISTINCT FCTLP.PostId ) - COUNT(DISTINCT cast(FCTLP.LanguageId + '' + FCTLP.CultureId as nvarchar(18)) )) AS PostsUniques
, COUNT(DISTINCT FCTLP.LanguageId) AS PostsPerLanguages
, COUNT(DISTINCT cast(FCTLP.LanguageId + '' + FCTLP.CultureId as nvarchar(18)) ) AS PostsPerLanguagesAndCultures
, COUNT(DISTINCT FCTLP.PostId ) AS PostsEnBaseATotales
,(SELECT COUNT(DISTINCT FCTL2.ThreadId)) AS ThreadsPerCurrentLanguageCulture
,(SELECT COUNT(DISTINCT FCTLP2.PostId)) AS PostsPerCurrentLanguageCulture
,(SELECT COUNT(DISTINCT FCTLP.PostId)) AS PostsTotals
FROM Hierarchy FC --Todos los idiomas disponibles
LEFT JOIN dbo.[Forums.Categories.Languages] FCL ON FCL.DomainId = FC.DomainId AND FCL.ForumId=FC.ForumId AND FCL.CategoryId = FC.CategoryId
AND FCL.LanguageId =@LanguageId
AND FCL.CultureId = @CultureId
LEFT JOIN dbo.[Forums.Categories.Threads] FCT
ON FCT.DomainId=FC.DomainId AND FCT.ForumId=FC.DomainId AND FCT.CategoryId = FC.CategoryId
LEFT JOIN dbo.[Forums.Categories.Threads.Languages] FCTL
ON FCTL.DomainId=FCT.DomainId AND FCTL.ForumId=FCT.DomainId AND FCTL.CategoryId = FCT.CategoryId
LEFT JOIN dbo.[Forums.Categories.Threads.Languages] FCTL2
ON FCTL2.DomainId=FCT.DomainId AND FCTL2.ForumId=FCT.DomainId AND FCTL2.CategoryId = FCT.CategoryId
AND FCTL2.LanguageId = ISNULL(@LanguageId,FCTL2.LanguageId) AND FCTL2.CultureId=ISNULL(@CultureId ,FCTL2.CultureId)
LEFT JOIN dbo.[Forums.Categories.Threads.Languages.Posts] FCTLP
ON FCTLP.DomainId = FCTL.DomainId AND FCTLP.ForumId=FCTL.ForumId AND FCTLP.CategoryId=FCTL.CategoryId AND FCTLP.ThreadId=FCTL.ThreadId
AND FCTLP.LanguageId=FCTL.LanguageId AND FCTLP.CultureId=FCTL.CultureId
LEFT JOIN dbo.[Forums.Categories.Threads.Languages.Posts] FCTLP2
ON FCTLP2.DomainId = FCTL.DomainId AND FCTLP2.ForumId=FCTL.ForumId AND FCTLP2.CategoryId=FCTL.CategoryId AND FCTLP2.ThreadId=FCTL.ThreadId
AND FCTLP2.LanguageId=ISNULL(@LanguageId,FCTLP2.LanguageId) AND FCTLP2.CultureId=ISNULL(@CultureId ,FCTLP2.CultureId)
LEFT JOIN dbo.[Forums.Categories] FC1 on FC1.ParentCategoryId = FC.CategoryId
WHERE NOT FC.ParentCategoryId IS NULL
GROUP BY FC.DomainId, FC.ForumId, FC.CategoryId, Fc.ParentCategoryId, FC.Name,FCT.ForumId ,FCL.Name,FCL.Description,FC.Sort ,FC.level
ORDER BY SORT
END
When we run this query then we find This Error .......
Msg 102, Level 15, State 1, Procedure Forums.Categories.SelectAllWithInfo, Line 10
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure Forums.Categories.SelectAllWithInfo, Line 16
Must declare the scalar variable "@CategoryId".
Msg 102, Level 15, State 1, Procedure Forums.Categories.SelectAllWithInfo, Line 20
Incorrect syntax near ')'.
so plz give me solution of this problem.........
June 30, 2010 at 1:27 am
I suppose you're not running this query on a SQL Server 2008.
I think you're on SQL Server 2005.
-- Gianluca Sartori
June 30, 2010 at 6:54 am
Try splitting this line:DECLARE @CategoryId Int =
(
SELECT CategoryId
FROM dbo.[Forums.Categories]
WHERE ForumId = @ForumId
AND ParentCategoryId IS NULL
); --12;
into:
DECLARE @CategoryId Int;
SELECT @CategoryID = CategoryId
FROM dbo.[Forums.Categories]
WHERE ForumId = @ForumId
AND ParentCategoryId IS NULL;
And let us know if that helps.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply