这个存储过程是在执行的时候有问题 提示说:消息 102,级别 15,状态 1,第 17 行
'@sql' 附近有语法错误。检查不出来、请大家帮忙看看。(主要是写法不知道哪里错误了,字段是虚拟的,不必在意字段的意思)set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc a
@startdate datetime,
@enddate datetime,
@departid int,
@userid int
as
begin
declare @depend varchar(1000),@sql varchar(8000)
set @sql = 'select deptname,username'
select @sql=@sql+',max(case name when '''+name+''' then icount else 0 end)['+name+']'
from (select distinct name from type ) as a
set @sql=@sql+' ,pay,expr1,expr2 from #temp group by a.id,b.id '
set @depend=''
if @startdate is not null and @enddate is not null
begin set @depend = isnull(@depend,'')+ ' AND ( a.time between @startdate and @enddate) ' end
if @departid is not null
begin set @depend = isnull(@depend,'')+ ' and (a.id = @departid) ' end
if @userid is not null
begin set @depend = isnull(@depend,'')+ ' AND (b.id = @userid) ' endexec (' SELECT a.id,b.id
into #temp
FROM a,b,c,WHERE a.id!=b.id '+ @depend+' @sql ')
end
'@sql' 附近有语法错误。检查不出来、请大家帮忙看看。(主要是写法不知道哪里错误了,字段是虚拟的,不必在意字段的意思)set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc a
@startdate datetime,
@enddate datetime,
@departid int,
@userid int
as
begin
declare @depend varchar(1000),@sql varchar(8000)
set @sql = 'select deptname,username'
select @sql=@sql+',max(case name when '''+name+''' then icount else 0 end)['+name+']'
from (select distinct name from type ) as a
set @sql=@sql+' ,pay,expr1,expr2 from #temp group by a.id,b.id '
set @depend=''
if @startdate is not null and @enddate is not null
begin set @depend = isnull(@depend,'')+ ' AND ( a.time between @startdate and @enddate) ' end
if @departid is not null
begin set @depend = isnull(@depend,'')+ ' and (a.id = @departid) ' end
if @userid is not null
begin set @depend = isnull(@depend,'')+ ' AND (b.id = @userid) ' endexec (' SELECT a.id,b.id
into #temp
FROM a,b,c,WHERE a.id!=b.id '+ @depend+' @sql ')
end
into #temp
FROM a,b,c,WHERE a.id!=b.id '+ @depend+' @sql ')
end
估计是这里的问题
into #temp
FROM a,b,c,WHERE a.id!=b.id '+ @depend+' ' +@sql+'')
into #temp
FROM a,b,c,WHERE a.id!=b.id '+ @depend+' @sql ')
endexec (' SELECT a.id,b.id
into #temp
FROM a,b,c,WHERE a.id!=b.id '+ @depend+@sql)
end
into #temp
FROM a,b,c,WHERE a.id!=b.id '+ @depend+' @sql ')
end这个是个变量哦!
exec (' SELECT a.id,b.id
into #temp
FROM a,b,c,
WHERE a.id!=b.id '+ @depend)exec (@sql)
另外,
把它分为两句执行
exec (' SELECT a.id,b.id
into #temp
FROM a,b,c,
WHERE a.id!=b.id '+ @depend)exec (@sql)这种是不行的 临时表似乎只能在一个exec中进行操作