if 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 行: '= ' 附近有语法错误。
请高手帮忙看看,要如何改才能正确?
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 行: '= ' 附近有语法错误。
请高手帮忙看看,要如何改才能正确?
比如
set @sql='select ''兩個'',1'
引号没成对
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'
这里的
@DisplayChar ='' Then '' Else @DisplayChar ='And FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',',''+@DisplayChar+'',')>0)' End
还有
@fieldsList=ISNULL(@fieldsList,'') + ',[' + FItemNum + '_qc]' + ',[' + FItemNum + '_qm]' From CR_RptItem
这里[' + FItemNum + '_qc]'这个就算在动态语句中用和普通语句中用都行不通哦
1、你的动态SQL语句写的有问题比如 ISNULL(@fieldsList,'') 里的(@fieldsList应该分离开,'Select @fieldsList=ISNULL('+(@fieldsList+','''')'2、[' + FItemNum + '_qc] 不明白你写成这样的干什么的。
Select @fieldsList=ISNULL(@fieldsList,'') + ',[' + FItemNum + '_qc]' + ',[' + FItemNum + '_qm]' From CR_RptItem
Where FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',','+@DisplayChar+',')>0) And FItemID < =200
Select @fieldsList1=ISNULL(@fieldsList1,'') + ',[' + FItemNum + '_qc]' + ',[' + FItemNum + '_qm]' From CR_RptItem
Where FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',','+@DisplayChar+',')>0) And FItemID >200
上面的这段代码是可以正常执行了,这个肯定了,因为我已经测试过了,但是由于参数@DisplayChar varchar(8000) ='', --这里改了一下
@DisplayCase varchar(8000) ='' --这里改了一下是可以为空,也可以不为空的,为空的话当然是查询全部,不为空则要加条件了,也就是上面的FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',','+@DisplayChar+',')>0)我看了一段类似代码利用 case when来判断可以实现的,但是不是我这种复杂的,所以不知如何下手了,至于[' + FItemNum + '_qc] 则是把取出的加上'_qc' 来实现真正的字段名,不知怎么讲了,要不高手帮改一改吧,头脑乱了,改来改去也改不好
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)
/*
這種字串里的單引號,至少要改成兩個'',而且,動態執行的語句,最好先print 出來,
放到查詢分析器,很容易就debug了。
比如你第一個字串里的賦值 select @fieldsList=ISNULL(@fieldsList,'')
==>select @fieldsList=ISNULL(@fieldsList,'''')
*/