SQL Server: pagination by dynamic sql and getting error

Question

SQL Server: pagination by dynamic sql and getting error

T.Zacks 3,996

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

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

Answer accepted by question author

2 additional answers

Your answer

Answer 1

MelissaMa-msft 24,246 Moderator

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.

T.Zacks 3,996 Reputation points

2021-08-04T10:15:32.653+00:00

I When i execute your script then i got this error
Msg 203, Level 16, State 2, Line 17
The name 'SELECT * FROM (SELECT
CAST(ROW_NUMBER() OVER (ORDER BY LastCSMDeliveredDate DESC) AS INT) AS 'RowNumber',
ID,
Ticker
from tblLastCSMDelivered csm JOIN tblClient c
ON csm.ClientCode=c.ClientCode
WHERE LastCSMDeliveredDate IS NOT NULL) X
WHERE CAST(X.RowNumber AS INT)>=0 AND CAST(X.RowNumber AS INT)<10 ' is not a valid identifier.
Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2021-08-04T10:19:08.283+00:00
No, you don't. You get that error when you execute a script that you don't show us.

More precisely, you get this error when you say:

EXEC @sql

`This line means "Execute the stored procedure of which the name is given in the variable @sql", and then it goes downhill from there.

Melissa's post avoids that problem. How, is left as an exercise to the reader.

However, you should not do as in Melissa's post. You should use a parameterised statement as I demonstrated in my post. This is a lot better for a number of reasons. Only to mention one: it's simpler!
T.Zacks 3,996 Reputation points

2021-08-04T16:30:30.463+00:00

Sorry Madam i made mistake that is why it did not worked but later i was it was perfectly working.

Thank you
Viorel 125.8K Reputation points

2021-08-04T18:03:23.073+00:00

Did you consider other suggestions and efforts too (unless you are interested in answers)?
MelissaMa-msft 24,246 Reputation points Moderator

2021-08-05T01:17:57.053+00:00

Hi @T.Zacks ,

Thanks for your confirmation.

You could use EXEC (@alenzi ) instead of EXEC @alenzi so that you could avoid the 'is not a valid identifier.' error.

I personally recommend to use EXECUTE sp_executesql @alenzi to avoid many errors. One point is to declare @alenzi as nvarchar(max).

You could also refer other suggestions mentioned by other experts.

Best regards,
Melissa

Answer 2

Viorel 125.8K

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

T.Zacks 3,996 Reputation points

2021-08-03T16:00:36.713+00:00

please see my edit section where i pasted screen shot. i am getting error event after i change code as you said. thanks
Viorel 125.8K Reputation points

2021-08-03T17:21:38.133+00:00

Which line generates the error? Use exec (@alenzi ) instead of exec @*SQL*. Show the new code and new errors.
T.Zacks 3,996 Reputation points

2021-08-06T12:00:35.897+00:00

Thank you sir for your help.

Answer 3

Erland Sommarskog 129.6K MVP Volunteer Moderator

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!

T.Zacks 3,996

Sir i execute this way as per your guide line but getting error Incorrect syntax near '@offset'.

DECLARE @PageSize INT,@PageIndex INT
    DECLARE @offset INT
    DECLARE @SQL NVARCHAR(MAX)  
    DECLARE @params NVARCHAR(MAX)
    SET @PageIndex=1
    SET @PageSize=10
    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)'


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

Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2021-08-04T12:03:16.953+00:00

I'm sorry about the syntax error, but I need to ask: Did it never occurred to you that you could try to find the syntax error yourself? It should be fairly simple to see what character that is missing.

Share via

SQL Server: pagination by dynamic sql and getting error

2 additional answers

Your answer