存储过程中的字符串连接 [英] 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屋！