A Transact-SQL Question
-
I have a WebForm that will display paging data to customers.My purpose is to let the customers can select any page number.The WebForm calls a stored procedure in SQL2000:
ALTER PROCEDURE MyProc (@RecordPerPage int, @PageNumber int ) AS select top @RecordPerPage * from MyTable where KeyField> select Max(KeyField) from (select top @RecordPerPage*(@PageNumber-1) KeyField from MyTable order by KeyField) a order by KeyFieldUnfortunately,this procedure has a syntax error.It seems that the 'TOP' keyword can not be followed by an Expression. How could I do? I do not want to dynamicaly generate SQL string in client side(there are a dozen of reasons) and -- This is a large table,I will not use DataAdapter.Fill and DataGrid.AllowPaging. Need your help! Thanks a lot! -
I have a WebForm that will display paging data to customers.My purpose is to let the customers can select any page number.The WebForm calls a stored procedure in SQL2000:
ALTER PROCEDURE MyProc (@RecordPerPage int, @PageNumber int ) AS select top @RecordPerPage * from MyTable where KeyField> select Max(KeyField) from (select top @RecordPerPage*(@PageNumber-1) KeyField from MyTable order by KeyField) a order by KeyFieldUnfortunately,this procedure has a syntax error.It seems that the 'TOP' keyword can not be followed by an Expression. How could I do? I do not want to dynamicaly generate SQL string in client side(there are a dozen of reasons) and -- This is a large table,I will not use DataAdapter.Fill and DataGrid.AllowPaging. Need your help! Thanks a lot!You should build your SQL statement as a nvarchar and then use sp_executesql:
DECLARE @query NVARCHAR(500) SET @query = N'select top ' + CAST(@RecordPerPage AS NVARCHAR(5)) + N'* from MyTable... sp_executesql @querylook at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts\_sp\_ea-ez\_2h7w.asp --> Nick