question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked TZacks-2728 commented

SQL Server: pagination by dynamic sql and getting error

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-1 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  


sql-server-transact-sql
ss.png (13.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered MelissaMa-msft commented

Hi @TZacks-2728,

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.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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!

0 Votes 0 ·

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

Thank you

0 Votes 0 ·

Did you consider other suggestions and efforts too (unless you are interested in answers)?

0 Votes 0 ·

Hi @TZacks-2728,

Thanks for your confirmation.

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

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

You could also refer other suggestions mentioned by other experts.

Best regards,
Melissa


0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog commented

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!

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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
0 Votes 0 ·

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.

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered TZacks-2728 commented

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://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#Offset. (Then ROW_NUMBER is not needed).




· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

Which line generates the error? Use exec (@SQL) instead of exec @SQL. Show the new code and new errors.

1 Vote 1 ·

Thank you sir for your help.

0 Votes 0 ·