存储过程中的字符串连接 [英] String concatenation in Stored Procedure
本文介绍了存储过程中的字符串连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Hello to All
我有一个商店程序,如
声明 @ CName as varchar ( 50 )= ' SalInvNo' 跨度>;
声明 @ qry as nvarchar ( 500 )
声明 @ count as int
set @ qry = ' select @count = count( *)来自表格;
设置@qry + =' 其中 ' + @ CName +'<> 0 ' ;
exec sp_executesql @ qry,N' @ count int OUTPUT ' ,@ count OUTPUT
但却出错:
Msg 245,16级,状态1,行1
将varchar值''SalINV1''转换为数据类型int时转换失败。
主题,整理代码块 - OriginalGriff [/ edit]
解决方案
为什么要为此构建并执行字符串?
执行字符串通常不是最佳做法,也存在一些危险。
http:// www .sommarskog.se / dynamic_sql.html [ ^ ]
在你的情况下,我会摆脱enti重新@qry的东西,只需将列名称硬编码到查询中。
试试这个
set @ qry + =''where''+ @ CName +''<> + @温度+;
Hello to All
I have a store procedure like
declare @CName as varchar(50)='SalInvNo';
declare @qry as nvarchar(500)
declare @count as int
set @qry='select @count = count(*) from table;
set @qry+=' where '+@CName+' <> 0';
exec sp_executesql @qry, N'@count int OUTPUT', @count OUTPUT
but it is giving error:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value ''SalINV1'' to data type int.
[edit]Subject, tidied code blocks - OriginalGriff[/edit]
解决方案
Why would you build and execute a string for this?
Executing strings is usually not best practice and there are some dangers to it as well.
http://www.sommarskog.se/dynamic_sql.html[^]
In your case I would get rid of the entire @qry stuff and simply ''hardcode'' the column name into the query.
try this
set @qry+='' where ''+@CName+'' <> ''''''+@Temp+'''''''';
这篇关于存储过程中的字符串连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文