Question

sql server 2008 存储过程 报列名无效

USE [callnew]
GO
/****** Object: StoredProcedure [dbo].[compiledayinfogs] Script Date: 11/04/2014 19:46:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[compiledayinfogs](
@startdate nvarchar(20),
@enddate nvarchar(20)
)
as
begin

declare @condition nvarchar(500)
declare @sql nvarchar(2000)
select @condition = ' '

if @startdate is not null and @startdate <> ' '
begin
select @condition = @condition + ' and convert(varchar(100),notetime,23) >= ''' + @startdate + ''''
end

if @enddate is not null and @enddate <> ' '
begin
select @condition = @condition + ' and convert(varchar(100),notetime,23) <= ''' + @enddate + ''''
end

select @sql='select showdate,totalbill,(nodeal+intimedeal) as deal,nodeal,intimedeal, outoftimedeal,outoftimenodeal from (
select convert(varchar(100),notetime,23) as showdate,count(*) as totalbill,
count(case when (billstatus=1 or billstatus=2)and (finishtime='' or finishtime is null) then 1 end) as nodeal ,
count(case when (billstatus<>1 or billstatus<>2)and (finishtime='' or finishtime is not null ) and (finishtime<completelimit)then 1 end) as intimedeal ,
count(case when (billstatus=1 or billstatus=2) and (finishtime='' or finishtime is null) and (convert(varchar(100),getdate(),23)>completelimit) then 1 end ) as outoftimedeal ,
count(case when (billstatus<>1 or billstatus<>2) and (finishtime='' or finishtime is not null ) and (finishtime > completelimit and completelimit < convert(varchar(100),getdate(),23) ) then 1 end) as outoftimenodeal
from tb_service3_bill where 1=1 '+ @condition +' group by convert(varchar(100),notetime,23) ) a order by showdate desc'

exec (@sql)

end

报错：

消息 207，级别 16，状态 1，第 1 行
列名 'nodeal' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'nodeal' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'outoftimedeal' 无效。

(1 行受影响)

Answer 1

查一下上述列的值有哪些吧.不正常的值会导致无法运行.

Answer 2

执行 exec(@sql)前，看看@sql的值是怎样的吧。

Answer 3

检查下列是否存在，拼写是否错误？》