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

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