SQL Server: Getting convertion error when use ROW_NUMBER function for pagination

Question

I have written a stored procedure which fetch data from table using pagination. I used the ROW_NUMBER function.

Here is my stored procedure code:

ALTER PROCEDURE USP_GetLastCSMSavedData
    (@Ticker VARCHAR(10)=NULL,
     @ClientName VARCHAR(10)=NULL,
     @LastCSMDate Datetime=NULL,
     @PageIndex INT = 1,
     @PageSize INT = 10)
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX)
    DECLARE @offset INT
    SET @offset = (@PageIndex - 1) * @PageSize
    
    SET @SQL = 'SELECT * FROM (SELECT 
    CAST(ROW_NUMBER() OVER (ORDER BY LastCSMDeliveredDate DESC) AS INT) AS ''RowNumber'',
    ID,
    Ticker,
    c.ClientName,
    Earnings,
    PrePost,
    IIF([QC-ViewAllContent] IS NULL,0,1) HasViewAllContent,
    IIF([QCCommentsContent] IS NULL,0,1) HasQCCommentsContent,
    InsertedOn,
    LastCSMDeliveredDate,
    Action,
    UserName
    from tblLastCSMDelivered csm JOIN tblClient c
    ON csm.ClientCode=c.ClientCode
    WHERE LastCSMDeliveredDate IS NOT NULL) X
    WHERE CAST(X.RowNumber AS INT)>='+@offset+' AND CAST(X.RowNumber AS INT)<'+(@offset+@PageSize)

    IF @Ticker IS NOT NULL
    BEGIN
        SET @SQL=@SQL+' AND X.Ticker='+@Ticker
    END

    IF @ClientName IS NOT NULL
    BEGIN
        SET @SQL=@SQL+' AND X.ClientName='+@ClientName
    END

    IF @LastCSMDate IS NOT NULL
    BEGIN
        SET @SQL=@SQL+' AND CONVERT(VARCHAR,X.LastCSMDeliveredDate,112)=CONVERT(VARCHAR,'+@LastCSMDate+',112)'
    END

    --EXEC @SQL
    PRINT @SQL
END

I assume for this line

WHERE CAST(X.RowNumber AS INT)>='+@offset+' AND CAST(X.RowNumber AS INT)<'+(@offset+@PageSize)

I am getting a runtime error

Msg 245, Level 16, State 1, Procedure USP_GetLastCSMSavedData, Line 16 [Batch Start Line 20]
Conversion failed when converting the varchar value

Please tell me what I have missed in my code. Thanks

Parameterise your parameters, and the error goes away. Why are you using dynamic SQL here at all, when there's nothing dynamic going on here either. That, too, will cause the error to go away. — Thom A
– Thom A ♦, Commented Aug 3, 2021 at 11:41
You aren't parametrising your parameters, @Mist ... N'...CAST(X.RowNumber AS INT)>='+@offset+N'...' isn't parametrising, it's injecting; it's 2021 SQL injection should have died like the Black Plague by now yet it's still alive and well in your code. But, again, why are you using dynamic SQL when the statement doesn't need to be dynamic? — Thom A
– Thom A ♦, Commented Aug 3, 2021 at 11:46
@Larnu i rarely work with sql server. i do not have very good knowledge in sql server. i make the sql dynamic because data will be fetch based on filter like ticker, clientname etc. without dynamic how could i do it? can you please post a sample code here. thanks — Mist
– Mist, Commented Aug 3, 2021 at 11:49
But you could just parametrise... Again, why not parametrise? There's no need for dynamic SQL. — Thom A
– Thom A ♦, Commented Aug 3, 2021 at 11:51

Zohar Peled · Accepted Answer · 2021-08-03 11:59:58Z

2

You are correct on your assumption that this line causes the error: WHERE CAST(X.RowNumber AS INT)>='+@offset+'...

Fact is that @offset is an int and the rest of the line is a string and therefor SQL Server will attempt to implicitly convert the string to int according to the data type precedence.

However, I Agree with Larnu's comment, you don't need dynamic SQL for this query at all:

ALTER PROC USP_GetLastCSMSavedData
(
    @Ticker VARCHAR(10)=NULL,
    @ClientName VARCHAR(10)=NULL,
    @LastCSMDate Datetime=NULL,
    @PageIndex INT = 1,
    @PageSize INT = 10
)
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX)
    DECLARE @offset INT
    SET @offset = (@PageIndex - 1) * @PageSize
    SELECT * FROM (
        SELECT CAST(ROW_NUMBER() OVER (ORDER BY LastCSMDeliveredDate DESC) AS INT) AS 'RowNumber',
        ID,
        Ticker,
        c.ClientName,
        Earnings,
        PrePost,
        IIF([QC-ViewAllContent] IS NULL,0,1) HasViewAllContent,
        IIF([QCCommentsContent] IS NULL,0,1) HasQCCommentsContent,
        InsertedOn,
        LastCSMDeliveredDate,
        Action,
        UserName
        from tblLastCSMDelivered csm 
        JOIN tblClient c
            ON csm.ClientCode=c.ClientCode
        WHERE LastCSMDeliveredDate IS NOT NULL
    ) X
    WHERE CAST(X.RowNumber AS INT)>= @offset  AND CAST(X.RowNumber AS INT)< (@offset+@PageSize)
    AND (@Ticker IS NULL OR X.Ticker = @Ticker)
    AND (@ClientName IS NULL OR X.ClientName = @ClientName)
    -- Why bother converting here?
    AND (@LastCSMDate IS NULL OR X.LastCSMDeliveredDate = @LastCSMDate) 
END

edited Aug 3, 2021 at 11:59

answered Aug 3, 2021 at 11:53

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Thom A Over a year ago

Probably want to add a OPTION (RECOMPILE) due to "Kitchen sink".

Mist Over a year ago

Thank you sir for the code. i will test it and let you know. this is not clear-> (@LastCSMDate IS NULL OR X.LastCSMDeliveredDate @LastCSMDate) why you did not here write like (@LastCSMDate IS NULL OR X.LastCSMDeliveredDate=@LastCSMDate) ? for other paremeter you wrote like this way.

Zohar Peled Over a year ago

It's just a typo... fixed.

Mist Over a year ago

Why bother converting here? you asked me. i want compare only date not time that is why i convert there. my approach was right ?

Mist Over a year ago

@ZoharPeled Sir if it would be dynamic sql then how could i handle this operation for which i was getting error. WHERE CAST(X.RowNumber AS INT)>= @offset AND CAST(X.RowNumber AS INT)< (@offset+@PageSize)

|

Collectives™ on Stack Overflow

SQL Server: Getting convertion error when use ROW_NUMBER function for pagination

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related