下面是我的SQL语句简化了的一个原型,
sql = "select d.id ,(select top 1 uid from [qx_za] where flag='d07' and ','+gsid+',' like '%,'+d.gsid_s+',%') as dk_r from [danbao] as d where 1=1"
conn.execute(sql)运行这条语句的时候提示:
Microsoft OLE DB Provider for SQL Server '80040e07'
在将 varchar 值 '%,' 转换成数据类型 int 时失败。[danbao] 表中的字段"d.gsid_s"值内类型为 int 型,存的都是其他表的id值
[qx_za]表中的字段'gsid'为varchar型,存的是id字符串,类似于 78,79,90,101,110 等等,
like这部分目的就是想找出[qx_za].gsid中包含[danbao].gsid_s的记录来我如果把
like '%,'+d.gsid_s+',%' 换成实际的ID值如 like '%79,%' 这条语句就好用了。请高手指点我这条语句该怎么写?
sql = "select d.id ,(select top 1 uid from [qx_za] where flag='d07' and ','+gsid+',' like '%,'+d.gsid_s+',%') as dk_r from [danbao] as d where 1=1"
conn.execute(sql)运行这条语句的时候提示:
Microsoft OLE DB Provider for SQL Server '80040e07'
在将 varchar 值 '%,' 转换成数据类型 int 时失败。[danbao] 表中的字段"d.gsid_s"值内类型为 int 型,存的都是其他表的id值
[qx_za]表中的字段'gsid'为varchar型,存的是id字符串,类似于 78,79,90,101,110 等等,
like这部分目的就是想找出[qx_za].gsid中包含[danbao].gsid_s的记录来我如果把
like '%,'+d.gsid_s+',%' 换成实际的ID值如 like '%79,%' 这条语句就好用了。请高手指点我这条语句该怎么写?
你response.write sql出来看看语句完整是什么样的,贴出来
'这样应该就可以了
sql = "select d.id ,(select top 1 uid from [qx_za] where flag='d07' and ','+gsid+',' like '%,'+ltrim(d.gsid_s)+',%') as dk_r from [danbao] as d where 1=1"
'response.write sql
conn.execute(sql)
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([userID] int,[name] nvarchar(7))
Insert #
select 111,N'name111' union all
select 222,N'name222' union all
select 333,N'name333'
Go
DECLARE @str NVARCHAR(1000)
SET @str='111,222,333'
Select * from # WHERE ','+@str+',' LIKE '%,'+RTRIM([userID])+',%'