USE [AdhocReportsDB_WEL]
GO
/****** Object: StoredProcedure [dbo].[spGetAllPatients] Script Date: 10/09/2017 11:14:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[spGetAllPatients]
(
@startIndex int,
@pageSize int,
@sortBy nvarchar(30),
@totalPatients int OUTPUT
)
AS
SET NOCOUNT ON
DECLARE
@sqlStatement nvarchar(max),
@upperBound int
IF @startIndex < 1 SET @startIndex = 1
IF @pageSize < 1 SET @pageSize = 1
SET @upperBound = @startIndex + @pageSize
Select @totalPatients=Count(*) From Patient
SET @sqlStatement = ' SELECT P.Pat_ID, P.Pat_Secondary_Identifier, P.Pat_Forename1, P.Pat_Surname, P.Pat_Sex_Code,P.Pat_DOB
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
FROM Patient
) AS P
WHERE rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
rowNumber < ' + CONVERT(varchar(9), @upperBound)
exec (@sqlStatement)