SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetSubscriptionPlans]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_GetSubscriptionPlans] GO -- ==================================================== -- Author: Magic Software -- Create date: 10-Jan-2018 -- Description: To get all the subscription plans of an edition -- ==================================================== create PROCEDURE [dbo].[usp_GetSubscriptionPlans] -- Add the parameters for the stored procedure here @iEditionId tinyint, @pageNo int, @pageLength int, @recordCount int out AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --Get the records on the basis of parameters page length and page number rows select LD.Id, LD.EditionId, LD.title, LD.Duration, LD.price, LD.IsActive from (Select ROW_NUMBER() OVER (ORDER BY Id) AS RowNo, CONVERT(NUMERIC(14,2), Price) as price, title, Id, EditionId, Duration, IsActive from subscriptionplan where subscriptionplan.EditionId=@iEditionId) as LD where RowNo > @pageLength * (@pageNo - 1) AND RowNo <= @pageLength * @pageNo --Calculate total number of records select @recordCount = count(ResultTable.Id) from (Select CONVERT(NUMERIC(14,2), Price) as price, title, Id, EditionId, Duration, IsActive from subscriptionplan where subscriptionplan.EditionId=@iEditionId) as ResultTable; END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO