在OpenRowSet函数中施用参数实例

在OpenRowSet函数中使用参数实例
以下是我自己使用中的实例,部分资料来源于网上,朋友们修改相关部分的内容后可在自己的环境下测试:
测试环境:
服务器:繁体win2003(sp2)+繁体sql2000(sp3)企业版
工作站:繁体xp(sp3)+简体sql2000(sp4)专业版

一.测试调用的存储过程
CREATE proc Hr_GetAllEmpl @year nvarchar(20)='0',@month nvarchar(20)='0',@Fields nvarchar(4000)='*',@OrderBy nvarchar(100)='',@Filter nvarchar(200)='' as declare @command nvarchar(4000),@empl_New nvarchar(2000),@command2 nvarchar(4000) if @year='0' or @month='0' or @Fields='*'--使用默認方法處理查詢數據 begin if @Fields='*' set @empl_New='service_empl_department,basic_depart_name,service_empl_part,basic_deppart_name,service_empl_index,service_empl_code,service_empl_name' else set @empl_New=@Fields set @command='select '+@empl_New+' from V_AllEmpl' end else--根據年月方法處理查詢數據 begin set @empl_New='case when isnull(p.admini_Code,'''')='''' or '''+@year+'-'+@month+'-1''<dateadd(d,day(v.service_empl_indate)*(-1)+1,v.service_empl_indate) then v.service_empl_code else (case when p.admini_date>v.service_empl_indate then p.admini_Code else p.admini_NewCode end) end as service_empl_code' set @command='select '+replace(@Fields,'service_empl_code',@empl_New)+' from v_allempl v left join ( select y.Admini_index,y.Admini_date,y.admini_Code,y.admini_NewCode from Admini_poschang y with(nolock) , (select Admini_index,min(Admini_date) as sd from Admini_poschang with(nolock) where Admini_date >= dateadd(mm,1,'''+@year+'-'+@month+'-1'') group by Admini_index) l where y.Admini_index = l.Admini_index and y.Admini_date = l.sd ) p on v.service_empl_index=p.admini_index' end if isnull(@Filter,'')<>'' set @command=@command+char(13)+char(10)+' where '+@Filter if isnull(@OrderBy,'')<>'' set @command=@command+char(13)+char(10)+' order by '+@OrderBy --print @command exec (@command) GO
二.调用方法实例
以下为调用所需要的参数
declare @cmd nvarchar(4000),@Year nvarchar(4),@Month nvarchar(2),@Fields nvarchar(500),@OrderBy nvarchar(200),@Filter nvarchar(200),@tmpFilter nvarchar(200) set @year=2012 set @month=5 set @Fields='*' set @OrderBy='service_empl_code' set @Filter='service_empl_zc=''01'''
1.OpenRowSet函数中的简单查询示例:
--说明:此处访问的数据表需要指定数据表所属数据库 --A.直接调用OpenRowSet函数 select * from openrowset('sqloledb','Trusted_Connection=yes','select * from data_20120604.dbo.v_allempl where service_empl_zc=''01'' order by service_empl_code') a--注意分隔符的转换:1层引用<'>变<''>2个<'> --B.动态调用调用OpenRowSet函数 set @cmd='select * from openrowset(''sqloledb'',''Trusted_Connection=yes'',''select * from data_20120604.dbo.v_allempl where service_empl_zc=''''01'''' order by service_empl_code'') a'--注意分隔符的转换:2层引用<''> 为<''''> print @cmd exec(@cmd)
2.在OpenRowSet函数的Select语句中使用参数:
--说明:此处访问的数据表需要指定数据表所属数据库 --A.直接调用OpenRowSet函数 --由于OpenRowSet中命令参数'query'是'字符串常量',所以直接使用OpenRowSet函数返回结果是无法使用表达式的,具体看OpenRowSet的帮文件 --B.动态调用调用OpenRowSet函数 set @tmpFilter=replace(@Filter,'''','''''')--注意分隔符的转换:2层引用<''> 为<''''>4个<'> print @tmpFilter set @cmd='select * from openrowset(''sqloledb'',''Trusted_Connection=yes'',''select '+@Fields+' from data_20120604.dbo.v_allempl where '+@tmpFilter+' order by '+@OrderBy+''') a' print @cmd exec(@cmd)

3.在OpenRowSet函数的存储过程中使用参数:

--A.直接调用OpenRowSet函数 --由于OpenRowSet中命令参数'query'是'字符串常量',所以直接使用OpenRowSet函数返回结果是无法使用表达式的,具体看OpenRowSet的帮文件 --B.动态调用调用OpenRowSet函数 set @tmpFilter=replace(@Filter,'''','''''''''')--注意分隔符的转换:3层引用<''> 为<''''''''>8个<'> print @tmpFilter set @cmd='select * from openrowset(''sqloledb'',''Trusted_Connection=yes'',''exec data_20120604.dbo.Hr_GetAllEmpl @year='+@Year+',@month='+@Month+',@Fields='''''+@Fields+''''',@OrderBy='''''+@OrderBy+''''',@Filter='''''+@tmpFilter+''''''') a' print @cmd exec(@cmd) /* 出现错误: Server: Msg 7355, Level 16, State 1, Line 1 OLE DB 提供程序 'sqloledb' 为列提供的元数据不一致。执行时更改了名称。 OLE DB 错误跟踪［Non-interface error: OLE DB provider returned different names for a column: ProviderName='sqloledb', CompileTimeColumnName='service_empl_index', RunTimeColumnName='service_empl_department', Rowset=exec data_20120604.dbo.Hr_GetAllEmpl @year=0,@month=5,@Fields='*',@OrderBy='service_empl_code',@Filter='service_empl_zc=''01'''］。 */

出现错误的原因:因为输入参数不同导致select返回结果列不一样造成的.也就是查询语句对过程可能的结果集进行了分析.具体的原因分析请看帖子:http://blog.csdn.net/cnjack/article/details/7659697
通过查sql2000的联机帮助，发现这个'set fmtonly设置项，很管用，哈哈
显然，这个开关项正是我们所想要分析器应该执行的结果，好试一下吧：

set @cmd='select * from openrowset(''sqloledb'',''Trusted_Connection=yes'',''set fmtonly off;exec data_20120604.dbo.Hr_GetAllEmpl @year='+@Year+',@month='+@Month+',@Fields='''''+@Fields+''''',@OrderBy='''''+@OrderBy+''''',@Filter='''''+@tmpFilter+''''''') a' print @cmd exec(@cmd)

搞定...

有什么问题大家可以留言一起讨论