مشكل در Inner Join و RowNum

[Mostafa_Dindar]

سلام به همه دوستان و اساتيد
ديگه دارم ديونه ميشم از اين كوئري . هر كاري كردم نشد كه نشد . يك StoredProcedure دارم كه Sorting and Paging رو پياده سازي كردم . مشكلي نداره و به درستي كار ميكنه ولي موندم چطور يك Inner Join ساده به اون اعمال كنم .
جدول مقاله :

CREATE TABLE [dbo].[Articles](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](250) COLLATE Arabic_CI_AS NOT NULL,
[CategoryId] [int] NOT NULL,
[Title] [nvarchar](255) COLLATE Arabic_CI_AS NOT NULL,
[Abstract] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
[Body] [nvarchar](max) COLLATE Arabic_CI_AS NOT NULL,
[ReleaseDate] [datetime] NULL,
[ExpireDate] [datetime] NULL,
[Approved] [bit] NOT NULL,

[Listed] [bit] NOT NULL,
[CommentEnabled] [bit] NOT NULL,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] NOT NULL,
[Votes] [int] NOT NULL,
[TotalRating] [int] NOT NULL,
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
جدول شاخه ها :

CREATE TABLE [dbo].[Category](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](250) COLLATE Arabic_CI_AS NOT NULL,
[Title] [nvarchar](50) COLLATE Arabic_CI_AS NOT NULL,
[Importance] [int] NOT NULL,
[Description] [nvarchar](300) COLLATE Arabic_CI_AS NULL,
[ImageUrl] [nvarchar](50) COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
StoredProcedure

StoredProcedure من براي جدول مقالات به شكل زير هست و دوباره تاكيد ميكنم به درستي كار ميكنه :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[sp_Articles_SelectByCategoryId]
@CategoryId int,
@startRowIndex int = -1,
@maximumRows int = -1,
@sortExpression nvarchar(50),
@recordCount int = NULL OUTPUT

AS

IF (@recordCount IS NOT NULL)
BEGIN
SET @recordCount = (SELECT COUNT(*) FROM [dbo].[Articles] WHERE [CategoryId] = @CategoryId
)
RETURN
END

IF LEN(@sortExpression) = 0
SET @sortExpression = 'Id'

DECLARE @sql nvarchar(4000)
SET @sql =
'SELECT
[Id],
[AddedDate],
[AddedBy],
[CategoryId],
[Title],
[Abstract],
[Body],
[ReleaseDate],
[ExpireDate],
[Approved],

[Listed],
[CommentEnabled],
[OnlyForMembers],
[ViewCount],
[Votes],
[TotalRating]
FROM
(SELECT

[Id],
[AddedDate],
[AddedBy],
[CategoryId],
[Title],
[Abstract],
[Body],
[ReleaseDate],
[ExpireDate],
[Approved],

[Listed],
[CommentEnabled],
[OnlyForMembers],
[ViewCount],
[Votes],
[TotalRating],
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum

FROM [dbo].[Articles]

WHERE CategoryId = ' + CONVERT(nvarchar(10), @CategoryId) + '
) as CategoryIdInfo
WHERE
((RowNum between (' + CONVERT(nvarchar(10), @startRowIndex) + ') AND ' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ' - 1)
OR ' + CONVERT(nvarchar(10), @startRowIndex) + ' = -1 OR ' + CONVERT(nvarchar(10), @maximumRows) + ' = -1)'

-- Execute the SQL query
EXEC sp_executesql @sql


--endregion

حالا ميخوام يك Inner Join ساده بزنم تا Title جدول Category رو هم تو كوئري اضافه كنم هر كاري كردم نشد .

كوئري جدولها + StoredProcedur رو هم ضميمه كردم

جدولها و StoredProcedure (http://barnamenevis.org/forum/attachment.php?attachmentid=42035&d=1262642856)

[محمد سلیم آبادی]

حالا ميخوام يك Inner Join ساده بزنم تا Title جدول Category رو هم تو كوئري اضافه كنم هر كاري كردم نشد .

سلام،
اینو امتحان کنین شاید جواب داد


SET @sql =
'SELECT
C1.[Id],
C1.[AddedDate],
C1.[AddedBy],
C1.[CategoryId],
C2.[Title]
C1.[Title],
C1.[Abstract],
C1.[Body],
C1.[ReleaseDate],
C1.[ExpireDate],
C1.[Approved],
C1.[Listed],
C1.[CommentEnabled],
C1.[OnlyForMembers],
C1.[ViewCount],
C1.[Votes],
C1.[TotalRating]
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
FROM [dbo].[Articles]
WHERE CategoryId = ' + CONVERT(nvarchar(10), @CategoryId) + ') as C1
INNER JION [dbo].[Category] AS C2
ON C1.[CategoryId] = C2.ID
WHERE ((RowNum between (' + CONVERT(nvarchar(10), @startRowIndex) + ')
AND ' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ' - 1)
OR ' + CONVERT(nvarchar(10), @startRowIndex) + ' = -1
OR ' + CONVERT(nvarchar(10), @maximumRows) + ' = -1)'

[Mostafa_Dindar]

ممنون .
متاسفانه همين sproc تمام وقت ديروزم رو گرفت . خوشبختانه مشكلم برطرف شد . من شبيه sproc مقاله Sorting Custom Paged Results (http://aspnet.4guysfromrolla.com/articles/032206-1.aspx) از Scott Michell كار كرده بودم ولي از آنجايي كه تعدادي از فيلدهاي جدول Article و Category يكسان بودند از روش ايشون نمتونستم برم . نكته اين بود كه SortExpression رو مقدار پيش فرض a.Id بايد ميدادم.

اين sproc كار ميكنه . تفاوتي كه من ايجاد كرده بودم ازپارامترهاي startRowIndex , maximumRow به صورت Magic Number استفاده كردم و در صورتي كه مقدار -1 داده شوند تمام ركوردها رو برميگردونه و ميتونيم در برنامه متدهايي با OverLoad متفاوت بسازيم . همچنين در صورتي كه پارامتر recordCount هر مقداري داشته باشد تعداد ركوردها رو برميگردونه كه براي Custom Paging كاملا ضروري هست . (نياز به يك Sproc ديگه از بين رفته ) .


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Articles_SelectByCategoryId]
@CategoryId int,
@startRowIndex int = -1,
@maximumRows int = -1,
@sortExpression nvarchar(50),
@recordCount int = NULL OUTPUT

AS

IF (@recordCount IS NOT NULL)
BEGIN
SET @recordCount = (SELECT COUNT(*) FROM [dbo].[Articles] WHERE [CategoryId] = @CategoryId
)
RETURN
END

IF LEN(@sortExpression) = 0
SET @sortExpression = 'a.Id'

DECLARE @sql nvarchar(4000)
SET @sql =
'SELECT
[Id],
[AddedDate],
[AddedBy],
[CategoryId],
[Title],
[Abstract],
,
[ReleaseDate],
[ExpireDate],
[Approved],

[Listed],
[CommentEnabled],
[OnlyForMembers],
[ViewCount],
[Votes],
[TotalRating],
[B] CategoryTitle
FROM
(SELECT

a.[Id],
a.[AddedDate],
a.[AddedBy],
a.[CategoryId],
a.[Title],
a.[Abstract],
a.,
a.[ReleaseDate],
a.[ExpireDate],
a.[Approved],
a.
[Listed],
a.[CommentEnabled],
a.[OnlyForMembers],
a.[ViewCount],
a.[Votes],
a.[TotalRating],
[B] b.Title as CategoryTitle ,
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum

FROM [dbo].[Articles] a INNER JOIN Category b on a.CategoryId = b.Id

WHERE a.CategoryId = ' + CONVERT(nvarchar(10), @CategoryId) + '
) as CategoryIdInfo
WHERE
((RowNum between (' + CONVERT(nvarchar(10), @startRowIndex) + ') AND ' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ' - 1)
OR ' + CONVERT(nvarchar(10), @startRowIndex) + ' = -1 OR ' + CONVERT(nvarchar(10), @maximumRows) + ' = -1)'

-- Execute the SQL query
EXEC sp_executesql @sql