sorting gridview columns pagewise when we use order by inthe select query

Here is an example of stored proc call where you can pass page number and count or rows returned as well as "IN" parameters. If needed, you can change them to your country code for example.

The key is <b>SELECT ROW_NUMBER() OVER (ORDER BY Name) AS Row</b>

in your GridView on grid sorting event add a call to DB to return only the data you'll need.

This is works faster if you have large data sets, so you don't need to wire all the data just a small chunk...

<pre>
CREATE PROCEDURE [dbo].[sp_test]
@list nvarchar(256),
@start int,
@count int
AS
BEGIN
DECLARE @sql nvarchar(256)
DECLARE @params nvarchar(256)
DECLARE @end int
SET @end = @start + @count - 1
SET @params = N'@start int, @count int, @end int'
SET @sql = N'
SELECT id, type, name
FROM TEST
WHERE ID IN (
SELECT ID FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Name) AS Row, ID
FROM TEST
WHERE TYPE IN (' + @list + N')
) PAGED
WHERE Row >= @start AND Row <= @end
)
'
PRINT @sql
EXEC SP_EXECUTESQL @sql, @params, @start, @count, @end
</pre>