当前位置: 代码迷 >> Sql Server >> sql导出EXCEL语句语法异常
  详细解决方案

sql导出EXCEL语句语法异常

热度:95   发布时间:2016-04-27 11:12:59.0
sql导出EXCEL语句语法错误!
declare @k int
declare @m int
declare @n int 
set @k=2
set @m=(@k-1)*10
set @[email protected]*10
EXEC master..xp_cmdshell 'bcp "select * from tempdb..temptable where id>@m and id<[email protected]" queryout d:test.xls -c -q -S"leiyf" -U"sa" -P"sa"'
 

语法错误提示:
SQLState = 42000, NativeError = 137
Error = [Microsoft][SQL Native Client][SQL Server]必须声明标量变量 "@m"。
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]未能准备语句。
NULL

怎么改正!谢谢!!

------解决方案--------------------
declare @k int
declare @m int
declare @n int 
declare @cmd varchar(200)
set @k=2
set @m=(@k-1)*10
set @[email protected]*10
set @cmd='bcp "select * from tempdb..temptable where id>'+cast(@m as varchar(20))+' and id<='+cast(@n as varchar(20))+'" queryout d:test.xls -c -q -S"leiyf" -U"sa" -P"sa"'

EXEC master..xp_cmdshell @cmd
------解决方案--------------------
SQL code
declare @k intdeclare @m intdeclare @n int  DECLARE @sql varchar(max)set @k=2set @m=(@k-1)*10set @[email protected]*10SET @sql='EXEC master..xp_cmdshell ''bcp "select * from tempdb..temptable where id>'+LTRIM(@m)+' and id<='+LTRIM(@n)+'" queryout d:test.xls -c -q -S"leiyf" -U"sa" -P"sa"'''EXEC(@sql)
  相关解决方案