SQL Stored Procedure to Delete Zero Value Sift Fields

PaddyMullaney
PaddyMullaney Member Posts: 59
edited 2007-03-20 in NAV Tips & Tricks
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

Comments

  • ara3n
    ara3n Member Posts: 9,258
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3n
    ara3n Member Posts: 9,258
    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]
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Waldo
    Waldo Member Posts: 3,412
    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

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog

Categories