USE [Demo]
GO
/****** Object:  StoredProcedure [dbo].[GetAppsDetails]    Script Date: 05/06/2011 19:17:34 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAppsDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAppsDetails]
GO
/****** Object:  Table [dbo].[APP_TYPES]    Script Date: 05/06/2011 19:17:36 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[APP_TYPES]') AND type in (N'U'))
DROP TABLE [dbo].[APP_TYPES]
GO
/****** Object:  Table [dbo].[CUSTOMER_APP_REVIEWS]    Script Date: 05/06/2011 19:17:36 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CUSTOMER_APP_REVIEWS]') AND type in (N'U'))
DROP TABLE [dbo].[CUSTOMER_APP_REVIEWS]
GO
/****** Object:  Table [dbo].[CUSTOMER_APP_REVIEWS]    Script Date: 05/06/2011 19:17:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CUSTOMER_APP_REVIEWS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CUSTOMER_APP_REVIEWS](
	[APP_TYPE_ID] [numeric](18, 0) NOT NULL,
	[REVIEW_TITLE] [nvarchar](400) NULL,
	[AUTHOR_NAME] [nvarchar](400) NULL,
	[REVIEW_DATE] [datetime] NULL,
	[REVIEW_TEXT] [nvarchar](max) NULL,
	[REVIEW_RATING] [nvarchar](50) NULL,
	[LAST_UPDATED_BY] [nvarchar](50) NULL,
	[LAST_UPDATED_DATE] [datetime] NULL
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[APP_TYPES]    Script Date: 05/06/2011 19:17:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[APP_TYPES]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[APP_TYPES](
	[APP_STORE_ID] [numeric](18, 0) NOT NULL,
	[APP_TYPE_ID] [numeric](18, 0) NOT NULL,
	[APP_TYPE_NAME] [nvarchar](100) NOT NULL,
	[APP_TYPE_DESC] [nvarchar](400) NOT NULL,
	[LAST_UPDATED_BY] [nvarchar](50) NULL,
	[LAST_UPDATED_DATE] [varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[GetAppsDetails]    Script Date: 05/06/2011 19:17:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAppsDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--EXEC [GetAppsDetails] 1,12
CREATE PROCEDURE [dbo].[GetAppsDetails] 
	@PageIndex INT,
	@RecordsPerPage INT
	--@TotalRecord INT OUTPUT 
AS  
BEGIN  
SET NOCOUNT ON  
	/*
	SELECT  A.APP_TYPE_NAME,A.APP_STORE_ID,R.REVIEW_TITLE,R.AUTHOR_NAME,R.REVIEW_DATE,
	R.REVIEW_RATING,R.REVIEW_TEXT FROM [dbo].[APP_TYPES] A
	INNER JOIN [CUSTOMER_APP_REVIEWS] R ON A.APP_TYPE_ID=R.APP_TYPE_ID
	*/
		
	Declare @startRowIndex INT;
	Declare @endRowIndex INT;
	set @endRowIndex = (@PageIndex * @RecordsPerPage);
	set @startRowIndex = (@endRowIndex - @RecordsPerPage) + 1;

	With RecordEntries as (
		SELECT ROW_NUMBER() OVER (ORDER BY A.APP_TYPE_ID ASC) as Row, A.APP_TYPE_ID, A.APP_TYPE_NAME,A.APP_STORE_ID,R.REVIEW_TITLE,R.AUTHOR_NAME,R.REVIEW_DATE,
		R.REVIEW_RATING,R.REVIEW_TEXT FROM [dbo].[APP_TYPES] A
		INNER JOIN [CUSTOMER_APP_REVIEWS] R ON A.APP_TYPE_ID=R.APP_TYPE_ID
	)
	Select APP_TYPE_ID, APP_TYPE_NAME, APP_STORE_ID, REVIEW_TITLE, AUTHOR_NAME, REVIEW_DATE, REVIEW_RATING, REVIEW_TEXT
	FROM RecordEntries
	WHERE Row between 
	@startRowIndex and @endRowIndex
	
	SELECT COUNT(*) FROM [dbo].[APP_TYPES] A
	INNER JOIN [CUSTOMER_APP_REVIEWS] R ON A.APP_TYPE_ID=R.APP_TYPE_ID
END 






' 
END
GO