SQL Stored Procedure to Delete Zero Value Sift Fields

PaddyMullaney

The following SQL SP will delete the rows in SIFT tables which have zero values. It builds the SQL Statements dynamically, so if a table is modified it will automtically pick these changes up.

Then re-indexing can be scheduled in SQL alos & the NavisionOptimisation tasks are not required.



GO
/****** Object: StoredProcedure [Admin].[pDeleteZeroSiftValues] Script Date: 03/12/2007 10:15:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [Admin].[pDeleteZeroSiftValues] (@DbName varchar(100),@CoName varchar (100)) as
Declare
@TableName varchar(100),
@TableID int,
@TableNamec varchar(100),
@TableNamec2 varchar(100),
@TableIDc int,
@ColumnNamec varchar(100),
@sqlDel varchar (500),
@SqlField varchar(50),
@sqlWhere varchar (500),
@sql varchar (1050),
@q varchar(1),
@nsql nvarchar(1050)

Set @q = char(39)
--Set @DbName = 'EddyTraining'
--Set @CoName = 'AccentUK'

Create table #SiftTableList (TableName varchar (100), TableID int)
Create table #SiftTableAndColumnList (TableName varchar (100), TableID int, [ColumnName] varchar(100))

set @sql =
'insert into #SiftTableList Select top 100 percent [name], [id] From '
+ '['
+ @DbName
+ '].[dbo].[sysobjects] Where xtype = '
+ @q
+ 'U'
+ @q
+ 'and [name] like '
+ @q
+ '%$%$%'
+ @q
+ 'and [name] like '
+ @q
+ @CoName
+ '%'
+ @q
+ 'and Right([name], 1) in ('
+ @q
+ '0'
+ @q
+ ','
+ @q
+ '1'
+ @q
+', '
+ @q
+'2'
+ @q
+', '
+ @q
+ '3'
+ @q
+', '
+ @q
+ '4'
+ @q
+', '
+ @q
+ '5'
+ @q
+', '
+ @q
+ '6'
+ @q
+ ', '
+ @q
+ '7'
+ @q
+ ', '
+ @q
+ '8'
+ @q
+ ', '
+ @q
+ '9'
+ @q
+ ') order by [name]'
--print @sql
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
Declare BuilFieldListCursor Cursor For
Select-- top 10
TableName
, [TableID]
from #SiftTableList
order by TableName

open BuilFieldListCursor
Fetch Next From BuilFieldListCursor
into @TableName, @TableID
@FETCH_STATUS = 0
Begin
set @sql =
'Insert into #SiftTableAndColumnList
Select '
+ @q
+ @TableName
+ @q
+', '
+ @q
+ convert(nvarchar(10), @TableID)
+ @q
+ ', [name]
from ['
+ @dbName
+ '].dbo.syscolumns c where convert(varchar(10),c.[id]) = '
+ convert(varchar(10),@TableID)
--print @sql
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
Declare BuildSQLStatement Cursor For
Select
TableName
, [TableID]
, [ColumnName]
from #SiftTableandColumnList
where [ColumnName] like 'S%'
order by TableName
open BuildSQLStatement
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
--print @TableNamec + ' ' + cast(@TableIDc as varchar(10)) + ' ' + @ColumnNamec
Set @SqlDel = 'Delete From .[dbo].'+ @TableNamec
Set @SqlWhere = ' Where '
Set @SqlField = @ColumnNamec
Set @SqlWhere = @SqlWhere + @SqlField + ' = 0 '
set @TableNamec2 = @TableNamec
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
@FETCH_STATUS = 0
Begin
Set @SqlDel = 'Delete From .[dbo].'+ @TableNamec
--Set @SqlWhere = ' Where '
Set @SqlField = @ColumnNamec
Set @SqlWhere =
case @TableNamec2 when @TableNamec
Then @SqlWhere + ' And ' + @SqlField + ' = 0 '
Else ' Where ' + @SqlField + ' = 0 '
end
set @TableNamec2 = @TableNamec
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
end
Close BuildSQLStatement
Deallocate BuildSQLStatement
Set @Sql = @SqlDel + @SqlWhere
print @Sql
print getdate()
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
print @Sql
print getdate()
Fetch Next From BuilFieldListCursor
into @TableName, @TableID
end
Close BuilFieldListCursor
Deallocate BuilFieldListCursor

Drop table #SiftTableList
Drop Table #SiftTableAndColumnList

ara3n

I ran it and it worked. one issue. If company name has space inbetween, you can't run the above statement.

Thanks again for sharing this.

ara3n

I've added the code to handle empty space in company name

GO
/****** Object: StoredProcedure [Admin].[pDeleteZeroSiftValues] Script Date: 03/12/2007 10:15:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [pDeleteZeroSiftValues] (@DbName varchar(100),@CoName varchar (100)) as
Declare
@TableName varchar(100),
@TableID int,
@TableNamec varchar(100),
@TableNamec2 varchar(100),
@TableIDc int,
@ColumnNamec varchar(100),
@sqlDel varchar (500),
@SqlField varchar(50),
@sqlWhere varchar (500),
@sql varchar (1050),
@q varchar(1),
@nsql nvarchar(1050)

Set @q = char(39)
--Set @DbName = 'EddyTraining'
--Set @CoName = 'AccentUK'

Create table #SiftTableList (TableName varchar (100), TableID int)
Create table #SiftTableAndColumnList (TableName varchar (100), TableID int, [ColumnName] varchar(100))

set @sql =
'insert into #SiftTableList Select top 100 percent [name], [id] From '
+ '['
+ @DbName
+ '].[dbo].[sysobjects] Where xtype = '
+ @q
+ 'U'
+ @q
+ 'and [name] like '
+ @q
+ '%$%$%'
+ @q
+ 'and [name] like '
+ @q
+ @CoName
+ '%'
+ @q
+ 'and Right([name], 1) in ('
+ @q
+ '0'
+ @q
+ ','
+ @q
+ '1'
+ @q
+', '
+ @q
+'2'
+ @q
+', '
+ @q
+ '3'
+ @q
+', '
+ @q
+ '4'
+ @q
+', '
+ @q
+ '5'
+ @q
+', '
+ @q
+ '6'
+ @q
+ ', '
+ @q
+ '7'
+ @q
+ ', '
+ @q
+ '8'
+ @q
+ ', '
+ @q
+ '9'
+ @q
+ ') order by [name]'
--print @sql
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
Declare BuilFieldListCursor Cursor For
Select-- top 10
TableName
, [TableID]
from #SiftTableList
order by TableName

open BuilFieldListCursor
Fetch Next From BuilFieldListCursor
into @TableName, @TableID
While @@FETCH_STATUS = 0
Begin
set @sql =
'Insert into #SiftTableAndColumnList
Select '
+ @q
+ @TableName
+ @q
+', '
+ @q
+ convert(nvarchar(10), @TableID)
+ @q
+ ', [name]
from ['
+ @dbName
+ '].dbo.syscolumns c where convert(varchar(10),c.[id]) = '
+ convert(varchar(10),@TableID)
--print @sql
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
Declare BuildSQLStatement Cursor For
Select
TableName
, [TableID]
, [ColumnName]
from #SiftTableandColumnList
where [ColumnName] like 'S%'
order by TableName
open BuildSQLStatement
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
--added code start
set @TableNamec = '[' + @TableNamec + ']'
--added code end
--print @TableNamec + ' ' + cast(@TableIDc as varchar(10)) + ' ' + @ColumnNamec
Set @SqlDel = 'Delete From [' + @DbName + '].[dbo].'+ @TableNamec 
Set @SqlWhere = ' Where '
Set @SqlField = @ColumnNamec
Set @SqlWhere = @SqlWhere + @SqlField + ' = 0 '
set @TableNamec2 = @TableNamec
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
--added code start
set @TableNamec = '[' + @TableNamec + ']'
--added code end

While @@FETCH_STATUS = 0
Begin
Set @SqlDel = 'Delete From [' + @DbName + '].[dbo].'+ @TableNamec
--Set @SqlWhere = ' Where '
Set @SqlField = @ColumnNamec
Set @SqlWhere =
case @TableNamec2 when @TableNamec
Then @SqlWhere + ' And ' + @SqlField + ' = 0 '
Else ' Where ' + @SqlField + ' = 0 '
end
set @TableNamec2 = @TableNamec
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
--added code start
set @TableNamec = '[' + @TableNamec + ']'
--added code end
end
Close BuildSQLStatement
Deallocate BuildSQLStatement
Set @Sql = @SqlDel + @SqlWhere
print @Sql
print getdate()
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
print @Sql
print getdate()
Fetch Next From BuilFieldListCursor
into @TableName, @TableID
end
Close BuilFieldListCursor
Deallocate BuilFieldListCursor

Drop table #SiftTableList
Drop Table #SiftTableAndColumnList

how to use it.

exec pDeleteZeroSiftValues 'databasename','Company name'

How to delete the stored procedure


drop proc [pDeleteZeroSiftValues]

Waldo

I had some problems with this version, due to the "case sensitivity" of my database. I did some changes and this works for me:

USE [NAV_TEST]
GO
/****** Object:  StoredProcedure [dbo].[pDeleteZeroSiftValues]    Script Date: 03/20/2007 11:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[pDeleteZeroSiftValues] (@DbName varchar(100),@CoName varchar (100)) as 
Declare 
@TableName varchar(100), 
@TableID int, 
@TableNamec varchar(100), 
@TableNamec2 varchar(100), 
@TableIDc int, 
@ColumnNamec varchar(100), 
@sqlDel varchar (500), 
@SqlField varchar(50), 
@sqlWhere varchar (500), 
@sql varchar (1050), 
@q varchar(1), 
@nsql nvarchar(1050) 

Set @q = char(39) 
--Set @DbName = 'EddyTraining' 
--Set @CoName = 'AccentUK' 

Create table #SiftTableList (TableName varchar (100), TableID int) 
Create table #SiftTableAndColumnList (TableName varchar (100), TableID int, [ColumnName] varchar(100)) 

set @sql = 
'insert into #SiftTableList Select top 100 percent [name], [id] From ' 
+ '[' 
+ @DbName 
+ '].[dbo].[sysobjects] Where xtype = ' 
+ @q 
+ 'U' 
+ @q 
+ 'and [name] like ' 
+ @q 
+ '%$%$%' 
+ @q 
+ 'and [name] like ' 
+ @q 
+ @CoName 
+ '%' 
+ @q 
+ 'and Right([name], 1) in (' 
+ @q 
+ '0' 
+ @q 
+ ',' 
+ @q 
+ '1' 
+ @q 
+', ' 
+ @q 
+'2' 
+ @q 
+', ' 
+ @q 
+ '3' 
+ @q 
+', ' 
+ @q 
+ '4' 
+ @q 
+', ' 
+ @q 
+ '5' 
+ @q 
+', ' 
+ @q 
+ '6' 
+ @q 
+ ', ' 
+ @q 
+ '7' 
+ @q 
+ ', ' 
+ @q 
+ '8' 
+ @q 
+ ', ' 
+ @q 
+ '9' 
+ @q 
+ ') order by [name]' 
--print @sql 
set @nsql = convert(nvarchar(1050), @sql) 
exec sp_executesql @nsql 
Declare BuilFieldListCursor Cursor For 
Select-- top 10 
TableName 
, [TableID] 
from #SiftTableList 
order by TableName 

open BuilFieldListCursor 
Fetch Next From BuilFieldListCursor 
into @TableName, @TableID 
While @@FETCH_STATUS = 0 
Begin 
set @sql = 
'Insert into #SiftTableAndColumnList 
Select ' 
+ @q 
+ @TableName 
+ @q 
+', ' 
+ @q 
+ convert(nvarchar(10), @TableID) 
+ @q 
+ ', [name] 
from [' 
+ @DbName 
+ '].dbo.syscolumns c where convert(varchar(10),c.[id]) = ' 
+ convert(varchar(10),@TableID) 
--print @sql 
set @nsql = convert(nvarchar(1050), @sql) 
--print @nsql
exec sp_executesql @nsql
Declare BuildSQLStatement Cursor For 
Select 
TableName 
, [TableID] 
, [ColumnName] 
from #SiftTableAndColumnList 
where [ColumnName] like 'S%' 
order by TableName 
open BuildSQLStatement 
Fetch Next From BuildSQLStatement 
into @TableNamec, @TableIDc, @ColumnNamec 
--added code start 
set @TableNamec = '[' + @TableNamec + ']' 
--added code end 
--print @TableNamec + ' ' + cast(@TableIDc as varchar(10)) + ' ' + @ColumnNamec 
Set @sqlDel = 'Delete From [' + @DbName + '].[dbo].'+ @TableNamec 
Set @sqlWhere = ' Where ' 
Set @SqlField = @ColumnNamec 
Set @sqlWhere = @sqlWhere + @SqlField + ' = 0 ' 
set @TableNamec2 = @TableNamec 
Fetch Next From BuildSQLStatement 
into @TableNamec, @TableIDc, @ColumnNamec 
--added code start 
set @TableNamec = '[' + @TableNamec + ']' 
--added code end 

While @@FETCH_STATUS = 0 
Begin 
Set @sqlDel = 'Delete From [' + @DbName + '].[dbo].'+ @TableNamec 
--Set @SqlWhere = ' Where ' 
Set @SqlField = @ColumnNamec 
Set @sqlWhere = 
case @TableNamec2 when @TableNamec 
Then @sqlWhere + ' And ' + @SqlField + ' = 0 ' 
Else ' Where ' + @SqlField + ' = 0 ' 
end 
set @TableNamec2 = @TableNamec 
Fetch Next From BuildSQLStatement 
into @TableNamec, @TableIDc, @ColumnNamec 
--added code start 
set @TableNamec = '[' + @TableNamec + ']' 
--added code end 
end 
Close BuildSQLStatement 
Deallocate BuildSQLStatement 
Set @sql = @sqlDel + @sqlWhere 
--print @sql 
--print getdate() 
set @nsql = convert(nvarchar(1050), @sql) 
exec sp_executesql @nsql 
--print @sql 
--print getdate() 
Fetch Next From BuilFieldListCursor 
into @TableName, @TableID 
end 
Close BuilFieldListCursor 
Deallocate BuilFieldListCursor 

Drop table #SiftTableList 
Drop Table #SiftTableAndColumnList