在OpenRowSet函数中使用参数实例

[cnjack]

以下是我自己使用中的实例,部分资料来源于网上,朋友们修改相关部分的内容后可在自己的环境下测试:
测试环境:
服务器:繁体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函数中的简单查询示例:

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函数的存储过程中使用参数:

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)

OK!

[还在加载中灬]

想说啥呢?

[Shawn]

Hehe! Top! Mark!

[cnjack]

大家也可看下我的博客中此篇文章http://blog.csdn.net/cnjack/article/details/7663656的更多资料.

[开着拖拉机泡妞]

顶一个，谢谢楼主分享

[發糞塗牆]

不错不错，也想请教一下这个函数性能如何？

[cnjack]

那要看存储过程吧,暂时没有考虑到这个,我这个主要是我使用需要做的.