I have what I believe is described as a Dynamic Stored Procedure, an abbreviated version is below
[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)
This works as expected when called by the following
Declare @WhereClause Nvarchar(2000)
Set @WhereClause= 'WHERE StatusTable.Status IN (''Have'',''Want'')
AND [CountryTable].Country IN (''Aden'')'
Execute spImageCard_GetStatusByCountryAlphabetically @WhereClause = @WhereClause
I am now trying to introduce what I believe is referred to a Pagination, my simplified standalone working example for this is
[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
Unfortunately when I try to introduce the relevant parts (@Parameters and the Offset and Fetch lines) I get numerous errors. Any assistance would be much appreciated
My latest version taking on board your comments about sqlString and cast is as follows:
[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
And my calling procedure is
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
The error I am receiving is as follows
Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'ROWS'. Msg 153, Level 15, State 2, Line 8 Invalid usage of the option Next in the FETCH statement.
Please note I have also tried N'at the start of each string and the error is the same
I finally managed to solve this and cannot believe how simple the error was to resolve once I had identified it but equally how easy the error is to make. When constructing the string ensure there is a space at the end of each of the string Parameters otherwise in my case when @OrderClause and @Pagination were concatenated it was reading as
ORDER BY [CountryTable].Country, [StampTable].CatalogueNoOffset (('+@PageNumberString+'-1)*'+@RowsInPageString+') ROWS Fetch Next '+@RowsInPageString+' Rows Only']
No space between CatalogueNo and Offset When I ran through many of my retained earlier efforts this was the fundamental problem but in different and often multiple locations