April 20, 2015 at 3:57 am
April 20, 2015 at 5:05 am
Check the blog posts I've written on:
1. Creating Stored Procedures with Dynamic Search (filter)[/url]
2. Creating Stored Procedures with Dynamic Search & Paging (Pagination)[/url]
3. Creating Stored Procedure with Dynamic Search, Paging and Sorting[/url]
4. You can also use the FETCH-OFFSET clause for Pagination if you are on SQL 2012 or more, [link[/url]].
April 20, 2015 at 5:08 am
Hey Manoj
Thanks for replying.but can you please provide me solution for my existing stored procedure with performance optimization if possible??
please.
April 20, 2015 at 5:32 am
I've not tested this, but something like this you can use as starter and do modifications to make it stable:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_PagedItems]
(
@ID int = NULL,
@Name varchar(50) = NULL,
@HolidayDate date = NULL,
@SortCol varchar(20) = '',
@Page int=1,
@RecsPerPage int=10 -- default size, you can change it or apply while executing the SP
)
AS
BEGIN
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int,
Name varchar(50),
HolidayDate date
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ID, Name,HolidayDate)
SELECT HolidaysId, HolidayDiscription, HolidayDate
FROM holiday
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
; WITH CTE_Results
AS (
SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN @SortCol = 'ID_Asc' THEN ID
END ASC,
CASE WHEN @SortCol = 'ID_Desc' THEN ID
END DESC,
CASE WHEN @SortCol = 'Name_Asc' THEN Name
END ASC,
CASE WHEN @SortCol = 'Name_Desc' THEN Name
END DESC,
CASE WHEN @SortCol = 'HolidayDate_Asc' THEN HolidayDate
END ASC,
CASE WHEN @SortCol = 'HolidayDate_Desc' THEN HolidayDate
END DESC
) AS ROWNUM,
ID,
Name,
HolidayDate
FROM #TempItems
WHERE
(@ID IS NULL OR ID = @ID)
AND (@Name IS NULL OR Name LIKE '%' + @Name + '%')
AND (@HolidayDate IS NULL OR HolidayDate = @HolidayDate)
)
SELECT
ID,
Name,
HolidayDate
FROM CTE_Results
WHERE
ROWNUM > @FirstRec
AND ROWNUM < @LastRec
ORDER BY ROWNUM ASC
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
END
GO
April 20, 2015 at 2:13 pm
The logic you are looking for would look like this:
DECLARE
-- possible values: 'Starts With', 'Is Equal to', 'Not Equal to'
@filterType varchar(20) = 'Starts With',
@filterValue varchar(100) = 'abc';
SELECT *
FROM dbo.Holiday
WHERE
(
(@filterType = 'Starts With' AND name LIKE ''+@filterValue+'%')
OR
(@filterType = 'Is Equal to' AND name = @filterValue)
OR
(@filterType = 'Not Equal to' AND name <> @filterValue)
OR
(ISNULL(@filterType,'') NOT IN ('Starts With','Is Equal to','Not Equal to'))
);
-- Itzik Ben-Gan 2001
April 20, 2015 at 9:53 pm
hey Alan
Thanks you so much for the answer
can you please post whole Stored procedure considering performance optimization as one of the member suggested me that dont need to use Temporary table as it would just waste memory and you havent showed sorting logic in your post.
So could you please post your optimized stored procedure if possible????
April 21, 2015 at 3:56 am
This is how i have done and i am getting expected output but still i want to take improvement suggestion from all of you if there is any.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolidayFetchList]
@pageno int,
@pagesize int,
@sortorder varchar(10),
@sortcolumn varchar(100),
@filter varchar(max),
@count int OUTPUT
AS
BEGIN
declare @Start int=(@pageno)*@pagesize;
declare @End int=@Start+@pagesize;
SET NOCOUNT ON;
DECLARE @tblHoliday AS TABLE
(HolidaysId int,HolidayDate date,HolidayDiscription nvarchar(500),HolidayName nvarchar(max),RN int)
declare @sql varchar(max)= '
select HolidaysId,HolidayDate,HolidayDiscription,HolidayDiscription as HolidayName,ROW_NUMBER() OVER
(ORDER BY '+@sortcolumn + ' '+@sortorder+' ) AS RN from Holiday
WHERE 1=1 '+@filter
print @sql
INSERT INTO @tblHoliday
exec (@sql)
select @count=COUNT(*) from @tblHoliday
print @count
select * from @tblHoliday where RN>@Start and RN<=@End order by RN
END
Please do give me any suggestion if you have any.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply