?
SET
?QUOTED_IDENTIFIER?
ON
?
GO
SET
?ANSI_NULLS?
ON
?
GO
ALTER
???
proc
?stp_ExportTable
????
@sqlstr
?
nvarchar
(
4000
),????????
--
查詢語(yǔ)句,如果查詢語(yǔ)句中使用了order?by?,請(qǐng)加上top?100?percent,注意,如果導(dǎo)出表/視圖,用上面的存儲(chǔ)過(guò)程
????
@orderstr
?
nvarchar
(
255
),????
--
Order?by?Field
????
@path
?
nvarchar
(
1000
),???????
--
文件存放目錄
????
@fname
?
nvarchar
(
250
),???????
--
文件名
????
@sheetname
?
varchar
(
250
)
=
''
??????
--
要?jiǎng)?chuàng)建的工作表名,默認(rèn)為文件名
as
?
????
declare
?
@err
?
int
,
@src
?
nvarchar
(
255
),
@desc
?
nvarchar
(
255
),
@out
?
int
????
declare
?
@obj
?
int
,
@constr
?
nvarchar
(
1000
),
@sql
?
varchar
(
4000
),
@fdlist
?
varchar
(
8000
)
????
declare
?
@IstmpTB
?
as
?
bit
????
declare
?
@tmpsql
??
as
?
varchar
(
4000
)
????
SET
?
@IstmpTB
=
0
????
--
參數(shù)檢測(cè)
????
if
?
isnull
(
@fname
,
''
)
=
''
?
set
?
@fname
=
'
temp.xls
'
????
if
?
isnull
(
@sheetname
,
''
)
=
''
?
set
?
@sheetname
=
replace
(
@fname
,
'
.
'
,
'
#
'
)
????
????
--
檢查文件是否已經(jīng)存在
????
if
?
right
(
@path
,
1
)
<>
'
'
?
set
?
@path
=
@path
+
'
'
????
create
?
table
?#tb(a?
bit
,b?
bit
,c?
bit
)
????
set
?
@sql
=
@path
+
@fname
????
--
數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句
????
Insert
?
into
?#tb?
exec
?master..xp_fileexist?
@sql
????
set
?
@sql
=
@path
+
@fname
????
????
if
?
exists
(
select
?
1
?
from
?#tb?
where
?a
=
1
)
?????
set
?
@constr
=
'
DRIVER={Microsoft?Excel?Driver?(*.xls)};DSN=
''''
;READONLY=FALSE
'
???????????
+
'
;CREATE_DB="
'
+
@sql
+
'
";DBQ=
'
+
@sql
????
else
?????
set
?
@constr
=
'
Provider=Microsoft.Jet.OLEDB.4.0;Extended?Properties="Excel?8.0;HDR=YES
'
????????
+
'
;DATABASE=
'
+
@sql
+
'
"
'
????
--
連接數(shù)據(jù)庫(kù)
????
--
print?'nn33'
????
exec
?
@err
=
sp_oacreate?
'
adodb.connection
'
,
@obj
?out
????
if
?
@err
<>
0
?
goto
?lberr
????
--
print?'nn44'
????
exec
?
@err
=
sp_oamethod?
@obj
,
'
open
'
,
null
,
@constr
????
if
?
@err
<>
0
?
goto
?lberr
????
--
構(gòu)造temp表的SQL
????
declare
?
@tbname
?sysname
????
set
?
@tbname
=
'
##tmp_
'
+
convert
(
varchar
(
38
),
newid
())
????
set
?
@sql
=
'
select?*?into?[
'
+
@tbname
+
'
]?from(
'
+
@sqlstr
+
'
)?a
'
????
--
print?@sql
????
exec
(
@sql
)
????
set
?
@IstmpTB
=
1
????
????
select
?
@sql
=
''
,
@fdlist
=
''
????
select
?
@fdlist
=
@fdlist
+
'
,[
'
+
a.name
+
'
]?
'
?????,
@sql
=
@sql
+
'
,[
'
+
a.name
+
'
]?
'
??????
+
case
?
when
?b.name?
in
(
'
char
'
,
'
nchar
'
,
'
varchar
'
,
'
nvarchar
'
)?
then
?????????
'
text(
'
+
cast
(
case
?
when
?a.length
>
255
?
then
?
255
?
else
?a.length?
end
?
as
?
varchar
)
+
'
)
'
???????
when
?b.name?
in
(
'
bit
'
,
'
int
'
,
'
bigint
'
,
'
tinyint
'
,
'
smallint
'
)?
then
?
'
int
'
???????
when
?b.name?
in
(
'
smalldatetime
'
,
'
datetime
'
)?
then
?
'
datetime
'
???????
when
?b.name?
in
(
'
money
'
,
'
smallmoney
'
)?
then
?
'
money
'
???????
else
?b.name?
end
????
FROM
?tempdb..syscolumns?a?
left
?
join
?tempdb..systypes?b?
on
?a.xtype
=
b.xusertype
????
where
?b.name?
not
?
in
(
'
image
'
,
'
text
'
,
'
uniqueidentifier
'
,
'
sql_variant
'
,
'
ntext
'
,
'
varbinary
'
,
'
binary
'
,
'
timestamp
'
)
?????
and
?a.id
=
(
select
?id?
from
?tempdb..sysobjects?
where
?name
=
@tbname
)
????
????
select
?
@sql
=
substring
(
@sql
,
2
,
2000
),
@fdlist
=
substring
(
@fdlist
,
2
,
2000
)
????
--
create?table
????
select
?
@sql
=
'
create?table?[
'
+
@sheetname
+
'
](
'
+
@sql
+
'
)
'
????
--
print?@sql
????
exec
?
@err
=
sp_oamethod?
@obj
,
'
execute
'
,
@out
?out,
@sql
????
if
?
@err
<>
0
?
goto
?lberr
????
--
print?'nn'
????
--
destroy?ole?object
????
exec
?
@err
=
sp_oadestroy?
@obj
????
if
?
@err
<>
0
?
goto
?lberr
????
--
print?'nn1'
????
--
導(dǎo)入數(shù)據(jù)
????
????
set
?
@sql
=
'
openrowset(
''
MICROSOFT.JET.OLEDB.4.0
''
,
''
Excel?8.0;HDR=YES
???????;DATABASE=
'
+
@path
+
@fname
+
'''
,[
'
+
@sheetname
+
'
$])
'
????
????
set
?
@tmpsql
=
'
insert?into?
'
+
@sql
+
'
(
'
+
@fdlist
+
'
)?select?
'
+
@fdlist
+
'
?from?[
'
+
@tbname
+
'
]
'
????
--
print?@tmpsql
????
if
?
@orderstr
?
is
?
not
?
null
?
or
?
@orderstr
<>
''
????
begin
????????
set
?
@tmpsql
=
@tmpsql
+
'
?order?by?
'
+
@orderstr
????
end
????
--
print?@tmpsql????
????
exec
(
@tmpsql
)
????
????
set
?
@sql
=
'
drop?table?[
'
+
@tbname
+
'
]
'
????
exec
(
@sql
)
????
set
?
@IstmpTB
=
0
????
????
return
?
0
lberr:
???
???????
EXEC
?sp_displayoaerrorinfo?
@obj
,?
@err
????
--
DELETE?TmpTable?While?Error?
????
IF
?
@IstmpTB
=
1
?
????
BEGIN
????????
set
?
@sql
=
'
drop?table?[
'
+
@tbname
+
'
]
'
????????
exec
(
@sql
)
????
END
????
return
?
-
1
lbexit:
????
SELECT
?
@sql
,
@constr
,
@fdlist
GO
SET
?QUOTED_IDENTIFIER?
OFF
?
GO
SET
?ANSI_NULLS?
ON
?
GO



























































































































更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力,如果您喜歡我的文章,感覺(jué)我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
