我的存储过程如下:
CREATE PROCEDURE dbo.dcxls AS
EXEC master..xp_cmdshell 'bcp "select xm from test.dbo.ggjl where gzzl=1 and tjrq=200704" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'
GO
我希望把gzzl和tjrq定为变量,请问应如何修改,并且在asp中如何输入变量值给存储过程?
CREATE PROCEDURE dbo.dcxls AS
EXEC master..xp_cmdshell 'bcp "select xm from test.dbo.ggjl where gzzl=1 and tjrq=200704" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'
GO
我希望把gzzl和tjrq定为变量,请问应如何修改,并且在asp中如何输入变量值给存储过程?
@gzzl varchar(10),
@tjrq varchar(10)
AS
declare @sql varchar(8000)
set @sql = 'bcp "select xm from test.dbo.ggjl where gzzl='+@gzzl+' and tjrq='+@tjrq+'" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'
EXEC master..xp_cmdshell @sql
GO
@gzzl int,
@tjrq varchar(20)
AS
declare @s nvarchar(200)
set @s='EXEC master..xp_cmdshell ''bcp "select xm from test.dbo.ggjl where gzzl='+ltrim(@gzzl)+' and tjrq='''+@tjrq+'''" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'''
exec(@s)
@gzzl varchar(10),
@tjrq varchar(10)
AS
SET NOCOUNT ON
declare @sql varchar(8000)
set @sql = 'bcp "select xm from test.dbo.ggjl WITH(NOLOCK) where gzzl='+@gzzl+' and tjrq='+@tjrq+'" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'
EXEC master..xp_cmdshell @sql
GO
在一楼的基础上加点点
CREATE PROCEDURE dbo.dcxls AS
EXEC master..xp_cmdshell 'bcp "select sfzmhm from test.dbo.ggjl where gzzl=1 and tjrq=200704" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'
GO
导出的sfzmhm(18位身份证明号码)最后三位在book1.xls中变为000,而且显示为科学记数法,请问应如何解决?
EXEC master..xp_cmdshell 'bcp "select cast(sfzmhm as varchar(18)) from test.dbo.ggjl where gzzl=1 and tjrq=200704" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'
GO
转换成字符串,就不是科学记数法
定义变量 参数传入,形成正确的sql 语句 掉用xp_cmdshell 执行。
CREATE PROCEDURE dbo.dcxls
@gzzl varchar(100),
@tjrq varchar(100)
AS
SET NOCOUNT ON
declare @sql nvarchar(4000)
set @sql = 'bcp "select xm from test.dbo.ggjl WITH(NOLOCK) where gzzl='+@gzzl+' and tjrq='+@tjrq+'" queryout "D:\book1.xls" -c -Stest -Usa -Ptest'
EXEC master..xp_cmdshell @sql
GO执行完再Excel 里设置数据格式
不行啊,在xls中仍是科学记数法