to pengdali:--create table T(no varchar(8000),id int) go insert into T values('1,2',1) insert into T values('1,2,3',2) go create proc aa @a text as select * from T print cast(@a as varchar(8000)) exec('select * from T where no in ('+@a+')')go exec aa '1' go delete from t drop proc aa go返回信息是: 1 服务器: 消息 245,级别 16,状态 1,行 1 将 varchar 值 '1,2' 转换为数据类型为 int 的列时发生语法错误。我想打印语句 exec('select * from T where no in ('+@a+')') 由于@a是text类型的,所以未遂。如果改为 exec('select * from T where no in ('''+@a+''')') 则顺利通过。却没有预期的答案。返回空集。请问你是怎么实现的呢?
create proc aa @a text as select * from T print cast(@a as varchar(8000)) exec('select * from T where no like (''%'+@a+'%'')')exec aa '1'
--create table T(no varchar(8000),id int) go insert into T values('b1',1) insert into T values('b3',2) --exec('select * from T where no in (''b1'',''b2'',''b3'')') go create proc aa @a text as exec('select * from T where no in ('+@a+')') go exec aa '''b1'',''b2''' go delete from t drop proc aa go测试成功通过。 感谢大力让我眼界开阔了! to nm_2j(二军) : 你说的输入参数太多的问题,我也碰到过,你看看我的调用,估计在前端输入这么一串字符也是可以的
2。使用动态SQL查询:declare @sql varchar(8000)
set @sql = 'select * from a1 where item in ('+@scope+')'
exec (@sql)
回登山者:没办法,我不能限制参数的长度(业务上)
我执行这句sql:
select @scope = '''bj00001'',''bj00002'''
报错:
assignment operator 运算不能以 text 数据类型作为参数。
@a text
as
exec('select * from T where no in ('+@a+')')
go
exec aa '1,2'
godrop proc aa
go
不行啊
游标里的问题我已经解决了,另外问一下,如果我要对text中的数据进行替换,比如把“|”替换为“,”,如何做?
谢谢大家,稍后给分!!
go
insert into T
values('1,2',1)
insert into T
values('1,2,3',2)
go
create proc aa
@a text
as
select * from T
print cast(@a as varchar(8000))
exec('select * from T where no in ('+@a+')')go
exec aa '1'
go
delete from t
drop proc aa
go返回信息是:
1
服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 '1,2' 转换为数据类型为 int 的列时发生语法错误。我想打印语句
exec('select * from T where no in ('+@a+')')
由于@a是text类型的,所以未遂。如果改为
exec('select * from T where no in ('''+@a+''')')
则顺利通过。却没有预期的答案。返回空集。请问你是怎么实现的呢?
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]为过程或函数 Ns_Ln_GetContractBillListFromSet 指定的参数太多。
串里不能包含逗号,否则sql server会认为是多个参数,我只有在数据库端做了。
@a text
as
select * from T
print cast(@a as varchar(8000))
exec('select * from T where no like (''%'+@a+'%'')')exec aa '1'
go
insert into T
values('b1',1)
insert into T
values('b3',2)
--exec('select * from T where no in (''b1'',''b2'',''b3'')')
go
create proc aa
@a text
as
exec('select * from T where no in ('+@a+')')
go
exec aa '''b1'',''b2'''
go
delete from t
drop proc aa
go测试成功通过。
感谢大力让我眼界开阔了!
to nm_2j(二军) :
你说的输入参数太多的问题,我也碰到过,你看看我的调用,估计在前端输入这么一串字符也是可以的