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.