Click here to Skip to main content
15,881,715 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
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........
Posted
Updated 26-Jul-16 5:53am
v5
Comments
[no name] 26-Jul-16 2:24am    
String Parameters Needs to be quoted. (' + @Topic + ')'; should be (' + ''' + @Topic + ''' + ')';
Thiyagu Arockiasamy 26-Jul-16 2:31am    
its not working sir. there is no one records search it.
[no name] 26-Jul-16 2:32am    
Yes I see, I was wrong, I missed that you are doing "IN". What data type is the field "Topic"?
Thiyagu Arockiasamy 26-Jul-16 2:50am    
yes sir kindly help
Thiyagu Arockiasamy 26-Jul-16 2:50am    
String field in Topic sir

Since you are doing this in a stored procedure - and using the IN clause twice - I'd suggest that you convert the CSV list into a temporary table, and use that instead.
That's not too complex: Using comma separated value parameter strings in SQL IN clauses[^] and it both gets round the "integer conversion" problem you are experiencing and should be more efficient.
 
Share this answer
 
Comments
Thiyagu Arockiasamy 26-Jul-16 3:08am    
yes its correct sir but not working for me
you will have to pass the value like in this format

JavaScript
Sp_MultiSearchNews '''371'',''382''',1,20


format the data in c# and pass it as parameter or write a function to append the string in the required format..
 
Share this answer
 
Comments
Thiyagu Arockiasamy 26-Jul-16 5:12am    
sir its only find the values in 371 and 382 Not like '371,382' or '382,380' values
Karthik_Mahalingam 26-Jul-16 5:27am    
use this format '''371'',''382'''
Thiyagu Arockiasamy 26-Jul-16 5:18am    
for example i have
371
382
371
371,382
382,380 records but its only search on

371
382
371
Karthik_Mahalingam 26-Jul-16 5:26am    
are you storing comma seperated value in table ?
Thiyagu Arockiasamy 26-Jul-16 5:26am    
yes sir
Hi friend,

While run give input like below. Your code is correct.

SQL
Exec Sp_MultiSearchNews '371,382,1,20'
 
Share this answer
 
Comments
Thiyagu Arockiasamy 27-Jul-16 2:14am    
Incorrect syntax near '*'.

In this Error is occured

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900