create PROCEDURE [dbo].[Sp_MultiSearchNews]
@Topic VARCHAR(200) = NULL,
@pageno int = NULL,
@pagesize int = NULL
AS
BEGIN
IF( @Topic = 'null' )
BEGIN
SET @Topic = NULL;
END;
DECLARE @sql NVARCHAR(MAX), @sqlPaging NVARCHAR(MAX);
SET @sql = 'select distinct NewsID,CompanyID,Companyname,NewsTopic,Topic from MulitSearch_PortalNews WHERE 1=1';
IF @Topic IS NOT NULL
SET @sql = @sql + ' and Topic IN (' + @Topic + ')';
DECLARE @count VARCHAR(MAX);
SET @count = 'select count(*) as TotalCount from ( ' + @sql + ' ) as T';
exec (' select CAST(ROW_NUMBER() OVER(ORDER BY NewsID) AS INT) AS Row, TotalRecords,NewsID,CompanyID,Companyname,NewsTopic,Topic
from ( SELECT ROW_NUMBER() OVER(ORDER BY NewsID ) AS Row,('+@count+') AS TotalRecords ,* from ( '+@sql+' ) as T ) as pageResult
where ( Row between (('+@pageno+' -1)* '+@pagesize+'+1) AND '+@pageno+' * '+@pagesize+' )' );
END;
What I have tried:
1) MulitSearch_Portal is a views table(views)
Topic - field is string in my table saved in comma separated values.
If i run comment
--Sp_MultiSearchNews '371,382',1,20
Below error is accrued
Conversion failed when converting the varchar value '371,382' to data type int.
i tired - SET @Topic = REPLACE(@Topic , ',' , ''' , ''') its not work properly.
how to pass string values In clause using sql. Purpose of procedure need multisearch.
Kindly help please........