SQL codeif exists (select name from sysobjects where name = 'spHbbbReport' and type = 'p')
drop procedure spHbbbReport
go
Create Procedure spHbbbReport
@OperTable varchar(20),
@DisplayChar varchar(8000) ='', --这里改了一下
@DisplayCase varchar(8000) ='' --这里改了一下
as
Declare @fieldsList varchar(8000)
Declare @fieldsList1 varchar(8000)
Declare @s varchar(2000)
Declare @s1 varchar(2000)
Declare @s2 varchar(8000)Select @s ='
Select @fieldsList=ISNULL(@fieldsList,'') + ',[' + FItemNum + '_qc]' + ',[' + FItemNum + '_qm]' From CR_RptItem
Where FItemID < =200 '
+Case when @DisplayChar is null or @DisplayChar ='' Then '' Else @DisplayChar ='And FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',',''+@DisplayChar+'',')>0)' End
+'Order by FCompanyID'
Exec(@s)Select @s1 = '
Select @fieldsList1=ISNULL(@fieldsList1,'') + ',[' + FItemNum + '_qc]' + ',[' + FItemNum + '_qm]' From CR_RptItem
Where FItemID >200 '
+Case when @DisplayChar is null or @DisplayChar = '' Then '' Else @DisplayChar ='And FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',',''+@DisplayChar+'',')>0)' End
+'Order by FCompanyID'
Exec(@s1)Print @fieldsList
Print @fieldsList1Select @s2 ='
SELECT FCompanyID,fname,fnumber,年,月' + @fieldsList + @fieldsList1 +' FROM '+@OperTable
+Case when @DisplayCase is null or @DisplayCase = '' Then '' Else @DisplayCase ='Where FCompanyID in ('+ @DisplayCase +')' End
Exec (@s2)GO
检查发现:
服务器: 消息 170,级别 15,状态 1,过程 spHbbbReport,行 15
第 15 行: '+ ' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spHbbbReport,行 22
第 22 行: '+ ' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spHbbbReport,行 31
第 31 行: '= ' 附近有语法错误。
请高手帮忙看看,要如何改才能正确?
应该写成 ISNULL(@fieldsList1,'''')
比如select a,@inta from table ,这样写,然后大家帮你拼字符,这样容易多了
Select @fieldsList=ISNULL(@fieldsList,'') + ',[' + FItemNum + '_qc]' + ',[' + FItemNum + '_qm]' From CR_RptItem
Where FItemID < =200 '
+Case when @DisplayChar is null or @DisplayChar ='' Then '' Else @DisplayChar ='And FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',',''+@DisplayChar+'',')>0)'