如何在SQL中的子句中传递字符串值？ [英] How to pass string values in clause in 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;

我尝试过的：



1）MulitSearch_Portal是一个观看表（浏览次数）



主题 - 字段我的表中的字符串是以逗号分隔的值保存的。



如果我运行评论

--Sp_MultiSearchNews'371,382'，1,20

产生以下错误

将varchar值'371,382'转换为数据类型int时转换失败。



i累了 - SET @Topic = REPLACE（@Topic，'，'，'''，'''）它不能正常工作。



如何传递字符串值In使用sql的子句。程序的目的需要多元化。

请帮助请........

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

推荐答案

因为你在存储中这样做过程 - 并使用IN子句两次 - 我建议你将CSV列表转换为临时表，然后使用它。

那不是太复杂：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]它们都得到了围绕你正在经历的整数转换问题并且应该更有效率。

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.

你必须传递这种格式的值

you will have to pass the value like in this format

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

格式化c＃中的数据并将其作为参数传递或编写函数以附加所需格式的字符串..

format the data in c# and pass it as parameter or write a function to append the string in the required format..

您好朋友，



运行时输入如下所示。你的代码是正确的。

Hi friend,

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

Exec Sp_MultiSearchNews '371,382,1,20'

这篇关于如何在SQL中的子句中传递字符串值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！