老大,你真强!如果我想在存储过程中判断文件是dbf还是xls,在决定调用哪一个过程该怎么办呢?就是说 if 文件为dbf then exec p_dBase3 'c:\a.dbf','a' if 文件为xls then exec p_excel 'c:\a.xls','sheet1','a'但我不知道该如何实现?
--示例select cast(a as money) from( select a='a' union all select '1' union all select '1.0' union all select '#1' union all select '$1' union all select '$12,1' union all select '12,1' union all select '12,1%' union all select '&12,1' union all select '@12,1' )a where isnumeric(a)=1
create proc p_excel
@filename nvarchar(1000), --要导入的excel文件名
@sheetname sysname, --要导入的excel文件的工作表名
@tbname sysname --导入到那个表中
as
declare @s varchar(8000)
set @s='insert ['+@tbname+']
select * from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 8.0;IMEX=1;HDR=YES;DATABASE='+@filename+'''
,['+@sheetname+'$])'
exec(@s)
go--调用
exec p_excel 'c:\a.xls','sheet1','a'
go
--导入dBase III文件的处理
create proc p_dBase3
@filename nvarchar(1000), --要导入的dBase文件名
@tbname sysname --导入到那个表中
as
declare @s varchar(8000),@i int
set @i=len(@filename)-charindex('\',reverse(@filename))+1
set @s='insert ['+@tbname+']
select * from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0''
,''dBase III;DATABASE='+left(@filename,@i)+'''
,''select * from ['+stuff(@filename,1,@i,'')+']'')'
print(@s)
go--调用
exec p_dBase3 'c:\a.dbf','a'
go
if 文件为dbf then exec p_dBase3 'c:\a.dbf','a'
if 文件为xls then exec p_excel 'c:\a.xls','sheet1','a'但我不知道该如何实现?
但是我依然不知道要怎样在存储过程中输出一个字符串,以便于前台的接收显示。各位帮帮我
一种是用 raiserror 返回错误,前台用错误处理来接收.
比如:delete后我想输出一个字符串,说明“已删除记录**条”
as
set nocount on
delete ....
select re='已经删除记录'+cast(@@rowcount as varchar)+'条'
as
delete from ...
raiserror('已经删除记录%d条',1,16,@@rowcount)
from(
select a='a'
union all select '1'
union all select '1.0'
union all select '#1'
union all select '$1'
union all select '$12,1'
union all select '12,1'
union all select '12,1%'
union all select '&12,1'
union all select '@12,1'
)a where isnumeric(a)=1