这是我的XML文件 <PhysicalDetail TableType="Relation"> <Sql> <![CDATA[ declare @sql varchar(4000) declare @tablename varchar(50) declare @JG varchar(4000) declare @XMMC varchar(50) declare @XH varchar(50) declare @LXMC varchar(50) declare @TJXM varchar(50) declare cursor1 cursor for select tablename,dbliem,comment,LX.dwselfdefine_table_id,dw_name,xm.column_id from xt_dwselfdefine lx left join dbo.XT_dwselfdefine_column XM on lx.dwselfdefine_table_id=xm.dwselfdefine_table_id left join XT_dwselfdefine_column_ckz ck on ck.column_id= xm.column_id where lx.dwselfdefine_table_id>'2000' and xm.systemcolumn='0'create table #tempB ( DJLSH VARCHAR(20) NOT NULL, XH varchar(50) not null, LXBH varchar(50) not null, LXMC Varchar(50) not null, TJXM Varchar(50) not null, XMMC varchar(50) not null, JG VARCHAR(2000)NOT NULL, CKFW varchar(50) not null, TS varchar(50) not null, XM VARCHAR(50) NOT NULL, DW varchar(50) not null ) open cursor1 fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM while(@@fetch_status=0) begin set @sql='select treat_id as DJLSH,'''+@XH+''', '''+@XH+''','''+@LXMC+''', '''+@TJXM+''', '''+@XMMC+''', JG= '+@JG+' , '' '','' '', XM=tianxr,'''' from '+ @tablename+' '+'where treat_id='''+@DJLSH+''''+'and '+@JG+' not like '''' ' insert into #tempB exec(@Sql) fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM endclose cursor1 deallocate cursor1select DJLSH,XH,LXBH,LXMC,TJXM,XMMC,JG, isnull(CKZ.biaoqsm,'') as CKFW, case when lx.dwselfdefine_table_id='2028' then '' when convert(float,JG)>convert(float,cankz_max) then '↑' when convert(float,JG)<convert(float,cankz_min) then '↓' else '' end TS, XM as JCYS, isnull(ckz.cankz_unit,'') as DW from #tempB as b LEFT JOIN XT_dwselfdefine_column_ckz CKZ ON CKZ.COLUMN_ID=b.TJXM left join xt_dwselfdefine lx on lx.dwselfdefine_table_id=b.lxbh left join XT_dwselfdefine_column xm on xm.column_id=b.tjxm order by lx.shunxh,xm.shunxh drop table #tempBmpB ]]> </Sql> <Parameters> <Parameter name="TJBH"> <Par name="@DJLSH" DataGridViewColumnName="DJLSH"></Par> </Parameter> </Parameters> <DataColumns> <Column name="PublishId" type="System.SqlColumn" SqlColumnName="DJLSH" isPrimaryKey="true" SelectWhere="true" PrimaryParName="@DJLSH"/> <Column name="PhysicalId" type="System.SelfValue" value="1"/> <Column name="GroupId" type="System.GroupId" WhereColumn="Sample,DepartmentId" GroupColumn="DepartmentId"/>" <Column name="DepartmentId" type="System.SqlColumn" SqlColumnName="lxbh"/> <Column name="Department" type="System.SqlColumn" SqlColumnName="lxmc"/> <Column name="ItemId" type="System.SqlColumn" SqlColumnName="TJXM"/> <Column name="ItemName" type="System.SqlColumn" SqlColumnName="xmmc"/> <Column name="Sample" type="System.SqlColumn" SqlColumnName="XH"/> <Column name="Place" type="System.SelfValue" value=""/> <Column name="ResultFlag" type="System.SelfValue" value="0"/> <Column name="ResultUnit" type="System.SqlColumn" SqlColumnName="DW"/> <Column name="ResultValue" type="System.SqlColumn" SqlColumnName="JG"/> <Column name="ResultNote" type="System.SqlColumn" SqlColumnName="TS"/> <Column name="ResultExplain" type="MK.WebNet.WebNetDB.CollectInfo.SqlDataClass.NullValue"/> <Column name="RefRange" type="System.SqlColumn" SqlColumnName="CKFW"/> <Column name="Doctor" type="System.SqlColumn" SqlColumnName="JCYS"/> <Column name="Fee" type="System.SelfValue" value="0"/> <Column name="Content" type="MK.WebNet.WebNetDB.CollectInfo.SqlDataClass.NullValue"/> <Column name="DISP_ORDER" type="System.DispOrder" WhereColumn="PhysicalId,DepartmentId"/> </DataColumns> </PhysicalDetail>
调试方法: 1.在游标中的set @sql='select ...' 语句之前, 把该拼接动态SQL所需的所有变量,都一一select显示出来. 2.暂时注释掉insert into #tempB exec(@Sql)语句, 把拼接后的动态语句@sql打印出来,以便调试.while(@@fetch_status=0) begin select @XH,@LXMC,@TJXM,@XMMC,@JG,@tablename,@DJLSH --> 显示拼接动态SQL所需的各变量值.
set @sql='select treat_id as DJLSH,'''+@XH+''', '''+@XH+''','''+@LXMC+''', '''+@TJXM+''', '''+@XMMC+''', JG= '+@JG+' , '' '','' '', XM=tianxr,'''' from '+ @tablename+' '+'where treat_id='''+@DJLSH+''''+'and '+@JG+' not like '''' '
print @sql --> 打印拼接后的SQL语句. -- insert into #tempB exec(@Sql)
fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM end
错误信息是"转换成数据类型 int 时失败", 而非说@DJLSH是空值. 建议用SQL Profiler工具跟踪一下.
这是SQL Profiler跟踪出来的语句exec sp_executesql N' declare @sql varchar(4000) declare @tablename varchar(50) declare @JG varchar(4000) declare @XMMC varchar(50) declare @XH varchar(50) declare @LXMC varchar(50) declare @TJXM varchar(50) declare cursor1 cursor for select tablename,dbliem,comment,LX.dwselfdefine_table_id,dw_name,xm.column_id from xt_dwselfdefine lx left join dbo.XT_dwselfdefine_column XM on lx.dwselfdefine_table_id=xm.dwselfdefine_table_id left join XT_dwselfdefine_column_ckz ck on ck.column_id= xm.column_id where lx.dwselfdefine_table_id>''2000'' and xm.systemcolumn=''0''create table #tempB ( DJLSH VARCHAR(20) NOT NULL, XH varchar(50) not null, LXBH varchar(50) not null, LXMC Varchar(50) not null, TJXM Varchar(50) not null, XMMC varchar(50) not null, JG VARCHAR(2000)NOT NULL, CKFW varchar(50) not null, TS varchar(50) not null, XM VARCHAR(50) NOT NULL, DW varchar(50) not null ) open cursor1 fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM while(@@fetch_status=0) begin --select @XH,@LXMC,@TJXM,@XMMC,@JG,@tablename,@DJLSH set @sql=''select treat_id as DJLSH,''''''+@XH+'''''', ''''''+@XH+'''''',''''''+@LXMC+'''''', ''''''+@TJXM+'''''', ''''''+@XMMC+'''''', JG= ''+@JG+'' , '''' '''','''' '''', XM=tianxr,'''''''' from ''+ @tablename+'' ''+''where treat_id=''''''+@DJLSH+''''''''+''and ''+@JG+'' not like '''''''' '' --print @sql insert into #tempB exec(@Sql) fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM endclose cursor1 deallocate cursor1select DJLSH,XH,LXBH,LXMC,TJXM,XMMC,JG, isnull(CKZ.biaoqsm,'''') as CKFW, case when lx.dwselfdefine_table_id=''2028'' then '''' when convert(float,JG)>convert(float,cankz_max) then ''↑'' when convert(float,JG)<convert(float,cankz_min) then ''↓'' else '''' end TS, XM as JCYS, isnull(ckz.cankz_unit,'''') as DW from #tempB as b LEFT JOIN XT_dwselfdefine_column_ckz CKZ ON CKZ.COLUMN_ID=b.TJXM left join xt_dwselfdefine lx on lx.dwselfdefine_table_id=b.lxbh left join XT_dwselfdefine_column xm on xm.column_id=b.tjxm order by lx.shunxh,xm.shunxh drop table #tempB ',N'@DJLSH int',@DJLSH=49874
错误信息是"转换成数据类型 int 时失败", 而非说@DJLSH是空值. 建议用SQL Profiler工具跟踪一下. 这句SQL的where treat_id='' 我用的是参数 where treat_id=@DJLSH 不知道为什么程序实际执行的时候变成了'' ,参数实际应该会是一个数字
手工复制粘贴到SSMS里执行有无问题? 把其中的37行改为如下试试.set @sql=''select treat_id as DJLSH,''''''+@XH+'''''', ''''''+@XH+'''''',''''''+@LXMC+'''''', ''''''+@TJXM+'''''', ''''''+@XMMC+'''''', JG= ''+@JG+'' , '''' '''','''' '''', XM=tianxr,'''''''' from ''+ @tablename+'' ''+''where treat_id=''''''+rtrim(@DJLSH)+''''''''+'' and ''+@JG+'' not like '''''''' ''
名称: 在将 varchar 值 'select treat_id as DJLSH,'2001', '2001','生化', '718', '直接胆红素(DBIL)', JG= ada , ' ',' ', XM=tianxr,'' from tj_treat_shengh where treat_id='' 转换成数据类型 int 时失败。
也就是说@DJLSH传到SQL语句中是个空值 导致隐式转换失败
select @DJLSH=isnull(@DJLSH,0)
我@DJLSH 并没有在SQL语句中声明
这个值是在XML文件中事先声明好 取得是dategridview中的值
<PhysicalDetail TableType="Relation">
<Sql>
<![CDATA[
declare @sql varchar(4000)
declare @tablename varchar(50)
declare @JG varchar(4000)
declare @XMMC varchar(50)
declare @XH varchar(50)
declare @LXMC varchar(50)
declare @TJXM varchar(50)
declare cursor1 cursor
for select tablename,dbliem,comment,LX.dwselfdefine_table_id,dw_name,xm.column_id
from xt_dwselfdefine lx
left join dbo.XT_dwselfdefine_column XM on lx.dwselfdefine_table_id=xm.dwselfdefine_table_id
left join XT_dwselfdefine_column_ckz ck on ck.column_id= xm.column_id
where lx.dwselfdefine_table_id>'2000' and xm.systemcolumn='0'create table #tempB
(
DJLSH VARCHAR(20) NOT NULL,
XH varchar(50) not null,
LXBH varchar(50) not null,
LXMC Varchar(50) not null,
TJXM Varchar(50) not null,
XMMC varchar(50) not null,
JG VARCHAR(2000)NOT NULL,
CKFW varchar(50) not null,
TS varchar(50) not null,
XM VARCHAR(50) NOT NULL,
DW varchar(50) not null
)
open cursor1
fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM
while(@@fetch_status=0)
begin
set @sql='select treat_id as DJLSH,'''+@XH+''', '''+@XH+''','''+@LXMC+''', '''+@TJXM+''', '''+@XMMC+''', JG= '+@JG+' , '' '','' '', XM=tianxr,'''' from '+ @tablename+' '+'where treat_id='''+@DJLSH+''''+'and '+@JG+' not like '''' '
insert into #tempB exec(@Sql)
fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM
endclose cursor1
deallocate cursor1select DJLSH,XH,LXBH,LXMC,TJXM,XMMC,JG,
isnull(CKZ.biaoqsm,'') as CKFW,
case
when lx.dwselfdefine_table_id='2028' then ''
when convert(float,JG)>convert(float,cankz_max) then '↑'
when convert(float,JG)<convert(float,cankz_min) then '↓'
else '' end TS,
XM as JCYS,
isnull(ckz.cankz_unit,'') as DW
from #tempB as b
LEFT JOIN XT_dwselfdefine_column_ckz CKZ ON CKZ.COLUMN_ID=b.TJXM
left join xt_dwselfdefine lx on lx.dwselfdefine_table_id=b.lxbh
left join XT_dwselfdefine_column xm on xm.column_id=b.tjxm
order by lx.shunxh,xm.shunxh
drop table #tempBmpB
]]>
</Sql>
<Parameters>
<Parameter name="TJBH">
<Par name="@DJLSH" DataGridViewColumnName="DJLSH"></Par>
</Parameter>
</Parameters> <DataColumns>
<Column name="PublishId" type="System.SqlColumn" SqlColumnName="DJLSH" isPrimaryKey="true" SelectWhere="true" PrimaryParName="@DJLSH"/>
<Column name="PhysicalId" type="System.SelfValue" value="1"/>
<Column name="GroupId" type="System.GroupId" WhereColumn="Sample,DepartmentId" GroupColumn="DepartmentId"/>"
<Column name="DepartmentId" type="System.SqlColumn" SqlColumnName="lxbh"/>
<Column name="Department" type="System.SqlColumn" SqlColumnName="lxmc"/>
<Column name="ItemId" type="System.SqlColumn" SqlColumnName="TJXM"/>
<Column name="ItemName" type="System.SqlColumn" SqlColumnName="xmmc"/>
<Column name="Sample" type="System.SqlColumn" SqlColumnName="XH"/>
<Column name="Place" type="System.SelfValue" value=""/>
<Column name="ResultFlag" type="System.SelfValue" value="0"/>
<Column name="ResultUnit" type="System.SqlColumn" SqlColumnName="DW"/>
<Column name="ResultValue" type="System.SqlColumn" SqlColumnName="JG"/>
<Column name="ResultNote" type="System.SqlColumn" SqlColumnName="TS"/>
<Column name="ResultExplain" type="MK.WebNet.WebNetDB.CollectInfo.SqlDataClass.NullValue"/>
<Column name="RefRange" type="System.SqlColumn" SqlColumnName="CKFW"/>
<Column name="Doctor" type="System.SqlColumn" SqlColumnName="JCYS"/>
<Column name="Fee" type="System.SelfValue" value="0"/>
<Column name="Content" type="MK.WebNet.WebNetDB.CollectInfo.SqlDataClass.NullValue"/>
<Column name="DISP_ORDER" type="System.DispOrder" WhereColumn="PhysicalId,DepartmentId"/>
</DataColumns>
</PhysicalDetail>
1.在游标中的set @sql='select ...' 语句之前, 把该拼接动态SQL所需的所有变量,都一一select显示出来.
2.暂时注释掉insert into #tempB exec(@Sql)语句, 把拼接后的动态语句@sql打印出来,以便调试.while(@@fetch_status=0)
begin
select @XH,@LXMC,@TJXM,@XMMC,@JG,@tablename,@DJLSH --> 显示拼接动态SQL所需的各变量值.
set @sql='select treat_id as DJLSH,'''+@XH+''', '''+@XH+''','''+@LXMC+''', '''+@TJXM+''', '''+@XMMC+''', JG= '+@JG+' , '' '','' '', XM=tianxr,'''' from '+ @tablename+' '+'where treat_id='''+@DJLSH+''''+'and '+@JG+' not like '''' '
print @sql --> 打印拼接后的SQL语句.
-- insert into #tempB exec(@Sql)
fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM
end
而且程序抛出的错误也不是说未声明变量 而是@DJLSH取到了空值
然后手工在SSMS中执行,以便调试.
建议用SQL Profiler工具跟踪一下.
declare @sql varchar(4000)
declare @tablename varchar(50)
declare @JG varchar(4000)
declare @XMMC varchar(50)
declare @XH varchar(50)
declare @LXMC varchar(50)
declare @TJXM varchar(50)
declare cursor1 cursor
for select tablename,dbliem,comment,LX.dwselfdefine_table_id,dw_name,xm.column_id
from xt_dwselfdefine lx
left join dbo.XT_dwselfdefine_column XM on lx.dwselfdefine_table_id=xm.dwselfdefine_table_id
left join XT_dwselfdefine_column_ckz ck on ck.column_id= xm.column_id
where lx.dwselfdefine_table_id>''2000'' and xm.systemcolumn=''0''create table #tempB
(
DJLSH VARCHAR(20) NOT NULL,
XH varchar(50) not null,
LXBH varchar(50) not null,
LXMC Varchar(50) not null,
TJXM Varchar(50) not null,
XMMC varchar(50) not null,
JG VARCHAR(2000)NOT NULL,
CKFW varchar(50) not null,
TS varchar(50) not null,
XM VARCHAR(50) NOT NULL,
DW varchar(50) not null
)
open cursor1
fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM
while(@@fetch_status=0)
begin
--select @XH,@LXMC,@TJXM,@XMMC,@JG,@tablename,@DJLSH
set @sql=''select treat_id as DJLSH,''''''+@XH+'''''', ''''''+@XH+'''''',''''''+@LXMC+'''''', ''''''+@TJXM+'''''', ''''''+@XMMC+'''''', JG= ''+@JG+'' , '''' '''','''' '''', XM=tianxr,'''''''' from ''+ @tablename+'' ''+''where treat_id=''''''+@DJLSH+''''''''+''and ''+@JG+'' not like '''''''' ''
--print @sql
insert into #tempB exec(@Sql)
fetch next from cursor1 into @tablename,@JG,@XMMC,@XH,@LXMC,@TJXM
endclose cursor1
deallocate cursor1select DJLSH,XH,LXBH,LXMC,TJXM,XMMC,JG,
isnull(CKZ.biaoqsm,'''') as CKFW,
case
when lx.dwselfdefine_table_id=''2028'' then ''''
when convert(float,JG)>convert(float,cankz_max) then ''↑''
when convert(float,JG)<convert(float,cankz_min) then ''↓''
else '''' end TS,
XM as JCYS,
isnull(ckz.cankz_unit,'''') as DW
from #tempB as b
LEFT JOIN XT_dwselfdefine_column_ckz CKZ ON CKZ.COLUMN_ID=b.TJXM
left join xt_dwselfdefine lx on lx.dwselfdefine_table_id=b.lxbh
left join XT_dwselfdefine_column xm on xm.column_id=b.tjxm
order by lx.shunxh,xm.shunxh
drop table #tempB
',N'@DJLSH int',@DJLSH=49874
建议用SQL Profiler工具跟踪一下.
这句SQL的where treat_id='' 我用的是参数 where treat_id=@DJLSH 不知道为什么程序实际执行的时候变成了'' ,参数实际应该会是一个数字
把其中的37行改为如下试试.set @sql=''select treat_id as DJLSH,''''''+@XH+'''''', ''''''+@XH+'''''',''''''+@LXMC+'''''', ''''''+@TJXM+'''''', ''''''+@XMMC+'''''', JG= ''+@JG+'' , '''' '''','''' '''', XM=tianxr,'''''''' from ''+ @tablename+'' ''+''where treat_id=''''''+rtrim(@DJLSH)+''''''''+'' and ''+@JG+'' not like '''''''' ''
rtrim(@DJLSH)的功能即是数值转字符的方法.