如何在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屋!