SQL Server: pagination by dynamic sql and getting error

T.Zacks 3,996 Reputation points
2021-08-03T12:53:21.157+00:00

please see my code example

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  
  
      
    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 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. i want to do this by dynamic sql. thanks

EDIT
@Viorel still getting error after changing code as you said

WHERE X.RowNumber between ' + cast(@offset as varchar(max)) + ' AND ' + cast(@offset+@PageSize - 1 as varchar(max))  

see the screenshot also

120209-ss.png

I found another way to compose this SQL with dynamic SQL. here sharing that code.

CREATE PROC USP_GetLastCSMSavedData    
(    
    @Ticker VARCHAR(10)='',    
    @ClientCode VARCHAR(10)='',    
    @LastCSMDate VARCHAR(10)='',    
    @PageIndex INT = 1,    
    @PageSize INT = 10    
)    
AS    
BEGIN    
  DECLARE @SQL VARCHAR(MAX)    
  DECLARE @ClientName VARCHAR(200)    
    
 IF @ClientCode<>''    
 BEGIN    
  SELECT @ClientName=ClientName FROM tblClient WHERE ClientCode=@ClientCode    
 END    
 ELSE    
 BEGIN    
  SET @ClientName=''    
 END    
    
    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,    
        IIF([Action]='I','INSERTED','UPDATED') AS [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 ='' OR X.Ticker = @Ticker)    
    AND (@ClientName ='' OR X.ClientName = @ClientName)    
    AND (@LastCSMDate ='' OR CAST(X.LastCSMDeliveredDate AS DATE)=CAST(@LastCSMDate AS DATE))     
    
    SELECT COUNT(*) AS CNT FROM tblLastCSMDelivered     
    WHERE LastCSMDeliveredDate IS NOT NULL    
    AND (@Ticker ='' OR Ticker = @Ticker)    
    AND (@ClientCode ='' OR ClientCode = @ClientCode)    
    AND (@LastCSMDate ='' OR CAST(LastCSMDeliveredDate AS DATE)=CAST(@LastCSMDate AS DATE))     
    
END    
Developer technologies | Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,221 Reputation points
    2021-08-04T02:23:10.89+00:00

    Hi @T.Zacks ,

    Please refer below and check whether it is working:

    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 NVARCHAR(MAX)    
         DECLARE @offset INT  
         SET @offset = (@PageIndex - 1) * @PageSize  
         SET @SQL=N'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)>='+cast(@offset as char(10))+' AND CAST(X.RowNumber AS INT)<'+cast((@offset+@PageSize) as char(10))  
      
         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  
      
         EXECUTE sp_executesql  @SQL  
         --PRINT @SQL  
     END  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Viorel 122.6K Reputation points
    2021-08-03T13:50:02.687+00:00

    Try another line:

    WHERE X.RowNumber between ' + cast(@offset as varchar(max)) + ' AND ' + cast(@offset+@PageSize - 1 as varchar(max))  
    

    Also check if OFFSET and FETCH can be used in your case: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#Offset. (Then ROW_NUMBER is not needed).

    1 person found this answer helpful.

  2. Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator
    2021-08-03T18:50:55.5+00:00

    The code should be:

    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)'
    

    And then you invoke the dynamic SQL with

    SELECT @params = N'@offset int,
                                     @pagesize int'
    EXEC sp_executesql @sql, @params, @offset, @pagesize
    

    You should handle the parameters you have (@Ticker etc), in the same way. You will find that this makes your code at lot easier to write and maintain!

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.