Why TableAdapter doesn't recognize @parameter

问题:

I am using table adapter Query configuration wizard in Visual studio 2013 for getting data from my database. For some queries like this:

SELECT *
FROM ItemsTable
ORDER BY date_of_creation desc, time_of_creation desc
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

it doesn't recognize the @pageNumber as a paremeter and it cannot generate function that has these arguments while it works fine for queries like:

Select Top (@count) * from items_table

Why does in first query tableadapter fail to generate function with mentioned arguments whereas it can generate function fine for second one for example: tableadapter.getDataByCount(?int count)

Am I forced to use stored procedure, if yes since I don't know anything about it how?

Update: The Problem exactly occurs in TableAdapter Configuration Wizard in DataSet editor (VS 2013) and it doesn't generate functions with these parameters some times it says @RowsPerPage should be declared! but it should generate a function with this arguments I found that it happens when we don't use @parameter_name in clause other than SELECT and WHERE for example in this query we used the, in Offset clause.

回答1:

I can't tell you how to fix it in ASP, but here is a simple stored procedure that should do the same thing:

CREATE PROCEDURE dbo.ReturnPageOfItems
(
    @pageNumber     INT,
    @rowsPerPage    INT
)
AS
BEGIN;
    SELECT  *
    FROM    dbo.ItemsTable
    ORDER BY    date_of_creation desc,
                time_of_creation desc
    OFFSET (@pageNumber - 1) * @rowsperpage ROWS
    FETCH NEXT @rowsPerPage ROWS ONLY;
END;

This will also perform better than simply passing the query, because SQL Server will take advantage of the cached query plan created for the procedure on its first execution. It is best practice not to use SELECT *, as that can cause maintenance trouble for you if there are schema changes to the table(s) involved, so I encourage you to spell out the columns in which you're actually interested. The documentation for the CREATE PROCEDURE command is available here, and it spells out the many various options you have in greater detail. However, the code above should work fine as is.

If you need to grant access to your application user so they can use this proc, that code is

GRANT EXECUTE ON OBJECT::dbo.ReturnPageOfItems TO userName;