单独执行没问题啊. select distinct DataDictTypeId, [Value], Display into #TempDataTypeItemTable From TblDictDataTypeItem where R3ConfigID <> '00000000-0000-0000-0000-000000000000'Declare @TempDataDictTypeId varchar(70) Declare @TempDescription Varchar(500) declare @TempValue Varchar(200)Declare @TempSql Varchar(8000) Set @TempSql = ''Declare @TempCount Int Set @TempCount = 0DECLARE Cursor_DataTypeItem CURSOR FOR select * from #TempDataTypeItemTableOpen Cursor_DataTypeItem Begin fetch next from Cursor_DataTypeItem into @TempDataDictTypeId,@TempDescription,@TempValue While @@FETCH_STATUS = 0 Begin Set @TempCount = 0 Select @TempCount = Count(DataDictTypeId) From TblDictDataTypeItem Where DataDictTypeId = @TempDataDictTypeId And [Value] = @TempValue And Display = @TempDescription And R3ConfigID <> '00000000-0000-0000-0000-000000000000' If(@TempCount Is Not Null And @TempCount > 1) Begin Set @TempSql = '' Set @TempSql = 'Delete From TblDictDataTypeItem Where Cast(DataDictTypeId as Varchar(70))=' + '''' + @TempDataDictTypeId + '''' + ' And [Value] = ' + '''' + @TempValue + '''' + ' And Display = ' + '''' + @TempDescription + ''' And R3ConfigID <> ''00000000-0000-0000-0000-000000000000''' Set @TempSql = @TempSql + ' And DataTypeItemId In (Select Top '+Cast((@TempCount - 1) as Varchar(30))+' DataTypeItemId From TblDictDataTypeItem Where Cast(DataDictTypeId as Varchar(70))=' + '''' + @TempDataDictTypeId + '''' + ' And [Value] = ' + '''' + @TempValue + '''' + ' And Display = ' + '''' + @TempDescription + ''' And R3ConfigID <> ''00000000-0000-0000-0000-000000000000'')' --Exec(@TempSql) Select @TempSql Set @TempSql = '' End Set @TempSql = '' fetch next from Cursor_DataTypeItem into @TempDataDictTypeId,@TempDescription,@TempValue End End Close Cursor_DataTypeItem DEALLOCATE Cursor_DataTypeItem Drop Table #TempDataTypeItemTable
加个isnull的处理吧,可能有时候count是没有记录的 Select @TempCount = isnull(Count(DataDictTypeId),1) From TblDictDataTypeItem Where DataDictTypeId = @TempDataDictTypeId And [Value] = @TempValue And Display = @TempDescription And R3ConfigID <> '00000000-0000-0000-0000-000000000000'
select distinct DataDictTypeId, [Value], Display into #TempDataTypeItemTable From TblDictDataTypeItem where R3ConfigID <> '00000000-0000-0000-0000-000000000000'Declare @TempDataDictTypeId varchar(70)
Declare @TempDescription Varchar(500)
declare @TempValue Varchar(200)Declare @TempSql Varchar(8000)
Set @TempSql = ''Declare @TempCount Int
Set @TempCount = 0DECLARE Cursor_DataTypeItem CURSOR
FOR
select * from #TempDataTypeItemTableOpen Cursor_DataTypeItem
Begin
fetch next from Cursor_DataTypeItem into @TempDataDictTypeId,@TempDescription,@TempValue While @@FETCH_STATUS = 0
Begin Set @TempCount = 0
Select @TempCount = Count(DataDictTypeId) From TblDictDataTypeItem Where DataDictTypeId = @TempDataDictTypeId And [Value] = @TempValue And Display = @TempDescription And R3ConfigID <> '00000000-0000-0000-0000-000000000000'
If(@TempCount Is Not Null And @TempCount > 1)
Begin
Set @TempSql = ''
Set @TempSql = 'Delete From TblDictDataTypeItem Where Cast(DataDictTypeId as Varchar(70))=' + '''' + @TempDataDictTypeId + '''' + ' And [Value] = ' + '''' + @TempValue + '''' + ' And Display = ' + '''' + @TempDescription + ''' And R3ConfigID <> ''00000000-0000-0000-0000-000000000000'''
Set @TempSql = @TempSql
+ ' And DataTypeItemId In (Select Top '+Cast((@TempCount - 1) as Varchar(30))+' DataTypeItemId From TblDictDataTypeItem Where Cast(DataDictTypeId as Varchar(70))=' + '''' + @TempDataDictTypeId + '''' + ' And [Value] = ' + '''' + @TempValue + '''' + ' And Display = ' + '''' + @TempDescription + ''' And R3ConfigID <> ''00000000-0000-0000-0000-000000000000'')'
--Exec(@TempSql)
Select @TempSql
Set @TempSql = ''
End
Set @TempSql = ''
fetch next from Cursor_DataTypeItem into @TempDataDictTypeId,@TempDescription,@TempValue
End
End
Close Cursor_DataTypeItem
DEALLOCATE Cursor_DataTypeItem
Drop Table #TempDataTypeItemTable
把各语句拿开单独执行都是没问题的,但只要一起执行时取到的 @TempCount 值就为 -1 所以造成删除语句执行失败.
加个isnull的处理吧,可能有时候count是没有记录的
Select @TempCount = isnull(Count(DataDictTypeId),1) From TblDictDataTypeItem Where DataDictTypeId = @TempDataDictTypeId And [Value] = @TempValue And Display = @TempDescription And R3ConfigID <> '00000000-0000-0000-0000-000000000000'