三个单引号 '''+@variable+''' 另外,转换成字符串,建议用cast(@variable as varchar),str会补空格的。
改成 CREATE proc [dbo].[Proc_Dispatch_Sum] --参数 @salesMan VARCHAR(50)='',--业务员 as if object_id('tempdb..#tmp_dispatch') is not null drop table #tmp_dispatchselect dKind,cCusCode,cDLCode,dDate,cInvCode,cInvName,iQuantity,iTaxUnitPrice,iSum, salesMan into #tmp_dispatch from OPENROWSET('SQLOLEDB', '192.168.1.XXX','sa', 'XXXXXX.','select ''Dis'' as dKind,d.cCusCode,d.cDLCode ,d.dDate , ds.cInvCode ,ds.cInvName,ds.iQuantity ,ds.iTaxUnitPrice ,ds.iSum ,d.cPersonCode as salesMan FROM t1 d inner join t2 ds on d.dlid=ds.dlid WHERE d.cPersonCode='''+str(@salesMan)+''' and d.dDate>=''2014-01-01'' ) '
最后一个括号,不要放在单引号里面 CREATE proc [dbo].[Proc_Dispatch_Sum] --参数 @salesMan VARCHAR(50)='',--业务员 as if object_id('tempdb..#tmp_dispatch') is not null drop table #tmp_dispatchselect dKind,cCusCode,cDLCode,dDate,cInvCode,cInvName,iQuantity,iTaxUnitPrice,iSum, salesMan into #tmp_dispatch from OPENROWSET('SQLOLEDB', '192.168.1.XXX','sa', 'XXXXXX.','select ''Dis'' as dKind,d.cCusCode,d.cDLCode ,d.dDate , ds.cInvCode ,ds.cInvName,ds.iQuantity ,ds.iTaxUnitPrice ,ds.iSum ,d.cPersonCode as salesMan FROM t1 d inner join t2 ds on d.dlid=ds.dlid WHERE d.cPersonCode='''+str(@salesMan)+''' and d.dDate>=''2014-01-01''')
'''+@variable+'''
另外,转换成字符串,建议用cast(@variable as varchar),str会补空格的。
CREATE proc [dbo].[Proc_Dispatch_Sum]
--参数
@salesMan VARCHAR(50)='',--业务员
as
if object_id('tempdb..#tmp_dispatch') is not null
drop table #tmp_dispatchselect dKind,cCusCode,cDLCode,dDate,cInvCode,cInvName,iQuantity,iTaxUnitPrice,iSum,
salesMan into #tmp_dispatch from OPENROWSET('SQLOLEDB', '192.168.1.XXX','sa', 'XXXXXX.','select ''Dis'' as dKind,d.cCusCode,d.cDLCode ,d.dDate ,
ds.cInvCode ,ds.cInvName,ds.iQuantity ,ds.iTaxUnitPrice ,ds.iSum ,d.cPersonCode as salesMan
FROM t1 d
inner join t2 ds on d.dlid=ds.dlid
WHERE d.cPersonCode='''+str(@salesMan)+''' and d.dDate>=''2014-01-01'' ) '
CREATE proc [dbo].[Proc_Dispatch_Sum]
--参数
@salesMan VARCHAR(50)='',--业务员
as
if object_id('tempdb..#tmp_dispatch') is not null
drop table #tmp_dispatchselect dKind,cCusCode,cDLCode,dDate,cInvCode,cInvName,iQuantity,iTaxUnitPrice,iSum,
salesMan into #tmp_dispatch from OPENROWSET('SQLOLEDB', '192.168.1.XXX','sa', 'XXXXXX.','select ''Dis'' as dKind,d.cCusCode,d.cDLCode ,d.dDate ,
ds.cInvCode ,ds.cInvName,ds.iQuantity ,ds.iTaxUnitPrice ,ds.iSum ,d.cPersonCode as salesMan
FROM t1 d
inner join t2 ds on d.dlid=ds.dlid
WHERE d.cPersonCode='''+str(@salesMan)+''' and d.dDate>=''2014-01-01''')