动态存储过程和分页

Question

我有一个我认为被描述为动态存储过程的东西，缩写版本在下面

[dbo].[spImageCard_GetStatusByCountryAlphabetically]
@WhereClause nvarchar(2000)
AS
BEGIN
SET NOCOUNT ON
Declare @SelectStatement nvarchar(1800)
Declare @OrderClause nvarchar(200)
Declare @FullStatement nvarchar(4000)
Set @SelectStatement = 'Select Distinct [StampTable].StampId,[SeriesTable].Series, [StampTable].CatalogueNo, [CountryTable].Country, [ImageTable].Image From StampTable
left Join SeriesTable on StampTable.Series = SeriesTable.SeriesId
left Join ImageTable on StampTable.Image = ImageTable.ImageId
left Join CountryTable on StampTable.Country = CountryTable.CountryId
left Join StampQuantatiesTable on StampTable.StampId = StampQuantatiesTable.StampId
Left Join QuantatiesTable on StampQuantatiesTable.QuantatiesId = QuantatiesTable.QuantatiesId
Left Join [StatusTable] on  QuantatiesTable.StatusId = StatusTable.StatusId '
Set @OrderClause = 'ORDER BY [CountryTable].Country, [StampTable].CatalogueNo'
Set @FullStatement = @SelectStatement + @WhereClause + @OrderClause

Execute (@FullStatement)

当以下调用时，这将按预期工作

Declare @WhereClause Nvarchar(2000)
Set @WhereClause= 'WHERE StatusTable.Status IN (''Have'',''Want'')
AND [CountryTable].Country IN (''Aden'')'
Execute spImageCard_GetStatusByCountryAlphabetically @WhereClause = @WhereClause

我现在试图介绍我认为被称为寻呼的东西，我的简化独立工作示例是

[dbo].[spPageReturnTest]
@PageNumber As INT,@RowsInPage As INT
AS
BEGIN
SET NOCOUNT ON
Select stampid, CatalogueNo From StampTable
Order by Stampid
Offset (@PageNumber-1)*@RowsInPage ROWS
Fetch Next @RowsInPage Rows Only

不幸的是，当我试图介绍相关部分(@Parameters和Offset和Fetch行(时，我遇到了很多错误。如有任何协助，将不胜感激

我的最新版本采纳了您对sqlString和cast的评论如下：

[dbo].[spImageCard_GetStatusByCountryAlphabetically]
@WhereClause nvarchar(2000), @PageNumber INT,@RowsInPage INT
AS
BEGIN
SET NOCOUNT ON
Declare @SelectStatement nvarchar(1650)
Declare @OrderClause nvarchar(200)
Declare @FullStatement nvarchar(4000)
Declare @Pagination nvarchar(150)
Declare @PageNumberString nvarchar(4)
Declare @RowsInPageString nvarchar(4)
Set @PageNumberString = Cast(@PageNumber As nvarchar(4))
Set @RowsInPageString = cast(@RowsInPage As nvarchar(4))
Set @SelectStatement = 'Select Distinct [StampTable].StampId,[SeriesTable].Series, [StampTable].CatalogueNo, [CountryTable].Country, [ImageTable].Image From StampTable
left Join SeriesTable on StampTable.Series = SeriesTable.SeriesId
left Join ImageTable on StampTable.Image = ImageTable.ImageId
left Join CountryTable on StampTable.Country = CountryTable.CountryId
left Join StampQuantatiesTable on StampTable.StampId = StampQuantatiesTable.StampId
Left Join QuantatiesTable on StampQuantatiesTable.QuantatiesId = QuantatiesTable.QuantatiesId
Left Join [StatusTable] on  QuantatiesTable.StatusId = StatusTable.StatusId '
Set @OrderClause = 'ORDER BY [CountryTable].Country, [StampTable].CatalogueNo'
Set @Pagination = 'Offset (('+@PageNumberString+'-1)*'+@RowsInPageString+') ROWS Fetch Next '+@RowsInPageString+' Rows Only'
Set @FullStatement = @SelectStatement + @WhereClause + @OrderClause + @Pagination
Execute sp_executesql @FullStatement

我的呼叫程序是

Declare @WhereClause Nvarchar(2000)
Declare @PageNumber INT
Declare @RowsInPage INT
Set @WhereClause= 'WHERE StatusTable.Status IN (''Have'',''Want'')
AND [CountryTable].Country IN (''Aden'')'
Execute spImageCard_GetStatusByCountryAlphabetically @WhereClause = @WhereClause,@PageNumber=1,@RowsInPage=3

我收到的错误如下

消息102，级别15，状态1，第8行"ROWS"附近的语法错误。Msg153，级别15，状态2，第8行FETCH语句。

请注意，我在每个字符串的开头也尝试过N，错误是相同的

Answer 1

我终于解决了这个问题，我不敢相信一旦我确定了这个错误，解决它有多简单，但同样，犯这个错误有多容易。在构造字符串时，确保每个字符串的末尾都有一个空格参数，否则在我的情况下，当@OrderClause和@Pagination连接在一起时，它读取为

ORDER BY [CountryTable].Country, [StampTable].CatalogueNoOffset (('+@PageNumberString+'-1)*'+@RowsInPageString+') ROWS Fetch Next '+@RowsInPageString+' Rows Only']

CatalogueNo和Offset之间没有空格当我完成了我之前保留的许多工作时，这是一个根本问题，但在不同的、通常是多个位置