Stored procedure that can handle pagination, sorting, and searching based on dynamic columns in Microsoft SQL Server:

-

Stored procedure that can handle pagination, sorting, and searching based on dynamic columns in Microsoft SQL Server:



CREATE PROCEDURE [dbo].[GetPagedData] ( @tableName NVARCHAR(100), @selectColumns NVARCHAR(MAX), @whereClause NVARCHAR(MAX), @orderByColumn NVARCHAR(100), @sortDirection NVARCHAR(10), @pageIndex INT, @pageSize INT ) AS BEGIN DECLARE @startIndex INT DECLARE @endIndex INT SET @startIndex = (@pageIndex - 1) * @pageSize + 1 SET @endIndex = @startIndex + @pageSize - 1 DECLARE @sql NVARCHAR(MAX) SET @sql = N' WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@orderByColumn) + ' ' + @sortDirection + ') AS RowNum, ' + @selectColumns + ' FROM ' + QUOTENAME(@tableName) + ' WHERE ' + @whereClause + ' ) SELECT ' + @selectColumns + ' FROM CTE WHERE RowNum >= ' + CAST(@startIndex AS NVARCHAR(20)) + ' AND RowNum <= ' + CAST(@endIndex AS NVARCHAR(20)) EXEC sp_executesql @sql END 


Here's how you can use this stored procedure:


@tableName - The name of the table you want to query.@selectColumns - The list of columns you want to select, separated by commas.@whereClause - The WHERE clause you want to apply to filter the results.@orderByColumn - The name of the column you want to use to sort the results.@sortDirection - The direction you want to sort the results (ASC or DESC).@pageIndex - The index of the page you want to retrieve.@pageSize - The number of records you want to retrieve per page.


Here's an example of how you can call the stored procedure:




EXEC GetPagedData 'Customers', 'CustomerID, CompanyName, ContactName, City, Country', 'Country = ''USA''', 'CompanyName', 'ASC', 1, 10



This will return the first page of 10 customers from the USA, sorted by company name in ascending order. 



Comments

Post a Comment

Popular posts from this blog

Gratuity calculation

-
 Gratuity is calculated based on the number of years an employee has worked with an organization and their last drawn salary. In India, the gratuity amount is calculated using the following formula: Gratuity = (Last drawn salary x 15 x Number of years worked) / 26 Where, Last drawn salary: the basic salary plus dearness allowance (if applicable) of the employee at the time of leaving the job. 15: represents the number of days in a month 26: represents the number of working days in a month Assuming that the employee has completed 5 years of continuous service, and their last drawn salary was Rs. 12 lakh per annum, their gratuity amount would be: Gratuity = (12,00,000 x 15 x 5) / 26 Gratuity = Rs. 13,84,615.38 Therefore, the gratuity amount for this employee after 5 years of service would be Rs. 13,84,615.38. If the salary of the employee was Rs. 20,000 per month in the 4th year and Rs. 60,000 per month in the 5th year, then the gratuity amount would be calculated as follows: Average...
Read more

What is VPS (Virtual Private Server)

-
VPS stands for Virtual Private Server. It is a type of web hosting service that allows users to have their own virtual server within a shared hosting environment.  A VPS is created through virtualization technology, which divides a physical server into multiple virtual servers, each with its own dedicated resources like CPU, RAM, storage, and operating system. This makes a VPS much more powerful and flexible than a shared hosting account, while also being more affordable than a dedicated server. Users can install and run their own software, configure their server settings, and have greater control over their hosting environment. VPS hosting is commonly used by businesses, developers, and website owners who need more control, flexibility, and resources than shared hosting can provide, but don't want to incur the higher costs of a dedicated server. many companies provide VPS hosting services. These companies offer various VPS hosting plans that differ in terms of server specificatio...
Read more

how to fix smart card not inserted properly Videocon d2h

-
Image
Let me share an experience with you. I was facing a recurring problem with my d2h set-top box where I would frequently see an error message on my TV screen. To temporarily fix the issue, I would turn off and on the set-top box. I also contacted Videocon d2h's customer care for help. After verifying my d2h VC number for identification, the customer care executive tried to reinitiate the service multiple times, but the issue persisted. They suggested sending an engineer to fix the problem for a charge of 200 rupees, but I decided to tackle the issue myself. Since the smart card in the d2h set-top box cannot be seen from outside, I decided to open it up using a screwdriver. Inside, I found a card inserted on the motherboard of the set-top box that resembled an old mobile SIM card and had a similar SIM slot. I hope this information helps someone facing a similar issue. [IMG: Smart card and slot] Upon opening the card slot, I noticed that there were instructions ...
Read more