Generic Routing to Test the Data in Table column

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  April 27, 2015 at 8:20 am

  #145674

  We are trying to develop the company standards for each table / Field which is very specific to our company. So we are not allowed to change the database (standard ERP).

  One Example is for the Items Table and its field to check the data.

  I created a Table

  CREATE TABLE [dbo].[MDM_Rules](

  [Site] [nchar](10) ,

  [DataSet] [nvarchar](50),

  [DataField] [nvarchar](50) ,

  [Responsibility] [nvarchar](50) ,

  [RuleDescription] [nvarchar](1000) ,

  [RuleSQLCode] [nvarchar](max) ,

  [DueDays] [int] NOT NULL

  ) ON [PRIMARY]

  One of the Example of MDM_Rule (Master Data Management Rule) is :

  INSERT INTO [ki].[dbo].[MDM_Rules]

  ([Site] /* physical Location of plant */

  ,[DataSet] /* Table Name */

  ,[DataField] /* Field Name

  ,[Responsibility] /* Dept responsible of enter information */

  ,[DueDays] /* Days given to enter the data */

  ,[RuleDescription] /* Description of Rule */

  ,[RuleSQLCode] /* SQL Code to run to check the value in the field */)

  VALUES

  ('Phil','Item', 'dock_time', 'Planning', 1, 'Dock To Stock must be entered', 'select phil_app.dbo.item.item from phil_app.dbo.item where phil_app.dbo.item.item = MDMDataField and phil_app.dbo.item.dock_time is null ' )

  In this example I am checking for a Field dock_time and making sure its entered. Just like this there will be many field which will have Data Rule associated with it.

  I would like to create a routine which will look like this

  For each Item record loop :

  for each MDM_Rules record where DataSet = 'item'

  'Geta SQL script from RuleSQLCode field

  'replace the MDMDataField with ItemNumber .

  'if the data is not correct store the results in the Temp Table to reporting.

  end loop MDM_Rules

  end loop item.