Back to OIM Explorer

dbo.QBM_PDialogTablePKNameCorrect

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 7.927 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FTPrimaryKeyInfo_BT source text reference
  • references source dbo.QBM_FTPrimaryKeyInfo_BTV source text reference
  • references source dbo.QBM_FTPrimaryKeyInfo_BTVP source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL329 lines
1CREATE PROCEDURE QBM_PDialogTablePKNameCorrect(2  @TablePattern varchar(64) = '%'3)4AS5BEGIN6  DECLARE @UID_DialogTable varchar(38)7  DECLARE @tablename varchar(30)8  DECLARE @Tabletype varchar(1)9  DECLARE @PK1Name varchar(30)10  DECLARE @PK2Name varchar(30)11  DECLARE @XUser nvarchar(64) = object_name(@@procid)12  DECLARE @Xdate datetime = getutcdate()13  SET XACT_ABORT OFF14  BEGIN TRY15    IF @TablePattern LIKE '%[%]%'16    BEGIN17      GOTO musterverarbeitung18    END19    SELECT20      TOP 1 @UID_DialogTable = t.UID_DialogTable,21      @Tabletype = t.TableType,22      @tablename = t.TableName,23      @PK1Name = isnull(t.PKName1,24      ''),25      @PK2Name = isnull(t.PKName2,26      '')27    FROM DialogTable t28      WITH(readpast)29    WHERE30      t.TableName = @TablePattern31    IF @Tabletype IN('B',32    'T')33    BEGIN34      IF EXISTS(35        SELECT TOP 1 136        FROM DialogTable t37        WITH(readpast)38      JOIN dbo.QBM_FTPrimaryKeyInfo_BT() v39        ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName1 = 140      WHERE41        isnull(t.PKName1, '') <> isnull(v.ColumnName, ''))42      BEGIN43        UPDATE DialogTable44        SET PKName1 = v.ColumnName,45        XDateUpdated = @Xdate,46        XUserUpdated = @XUser47        FROM DialogTable t48          WITH(readpast)49        JOIN dbo.QBM_FTPrimaryKeyInfo_BT() v50          ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName1 = 151        WHERE52          isnull(t.PKName1,53        '') <> isnull(v.ColumnName,54        '')55      END56      IF EXISTS(57        SELECT TOP 1 158        FROM DialogTable t59        WITH(readpast)60      JOIN dbo.QBM_FTPrimaryKeyInfo_BT() v61        ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName2 = 162      WHERE63        isnull(t.PKName2, '') <> isnull(v.ColumnName, ''))64      BEGIN65        UPDATE DialogTable66        SET PKName2 = v.ColumnName,67        XDateUpdated = @Xdate,68        XUserUpdated = @XUser69        FROM DialogTable t70          WITH(readpast)71        JOIN dbo.QBM_FTPrimaryKeyInfo_BT() v72          ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName2 = 173        WHERE74          isnull(t.PKName2,75        '') <> isnull(v.ColumnName,76        '')77      END78      GOTO endLabel79    END80    IF @Tabletype IN('V')81    BEGIN82      IF EXISTS(83        SELECT TOP 1 184        FROM DialogTable t85        WITH(readpast)86      JOIN dbo.QBM_FTPrimaryKeyInfo_BTV() v87        ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName1 = 188      WHERE89        isnull(t.PKName1, '') <> isnull(v.ColumnName, ''))90      BEGIN91        UPDATE DialogTable92        SET PKName1 = v.ColumnName,93        XDateUpdated = @Xdate,94        XUserUpdated = @XUser95        FROM DialogTable t96          WITH(readpast)97        JOIN dbo.QBM_FTPrimaryKeyInfo_BTV() v98          ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName1 = 199        WHERE100          isnull(t.PKName1,101        '') <> isnull(v.ColumnName,102        '')103      END104      IF EXISTS(105        SELECT TOP 1 1106        FROM DialogTable t107        WITH(readpast)108      JOIN dbo.QBM_FTPrimaryKeyInfo_BTV() v109        ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName2 = 1110      WHERE111        isnull(t.PKName2, '') <> isnull(v.ColumnName, ''))112      BEGIN113        UPDATE DialogTable114        SET PKName2 = v.ColumnName,115        XDateUpdated = @Xdate,116        XUserUpdated = @XUser117        FROM DialogTable t118          WITH(readpast)119        JOIN dbo.QBM_FTPrimaryKeyInfo_BTV() v120          ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName2 = 1121        WHERE122          isnull(t.PKName2,123        '') <> isnull(v.ColumnName,124        '')125      END126      GOTO endLabel127    END128    IF @Tabletype IN('P')129    BEGIN130      IF EXISTS(131        SELECT TOP 1 1132        FROM DialogTable t133        WITH(readpast)134      JOIN dbo.QBM_FTPrimaryKeyInfo_BTVP() v135        ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName1 = 1136      WHERE137        isnull(t.PKName1, '') <> isnull(v.ColumnName, ''))138      BEGIN139        UPDATE DialogTable140        SET PKName1 = v.ColumnName,141        XDateUpdated = @Xdate,142        XUserUpdated = @XUser143        FROM DialogTable t144          WITH(readpast)145        JOIN dbo.QBM_FTPrimaryKeyInfo_BTVP() v146          ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName1 = 1147        WHERE148          isnull(t.PKName1,149        '') <> isnull(v.ColumnName,150        '')151      END152      IF EXISTS(153        SELECT TOP 1 1154        FROM DialogTable t155        WITH(readpast)156      JOIN dbo.QBM_FTPrimaryKeyInfo_BTVP() v157        ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName2 = 1158      WHERE159        isnull(t.PKName2, '') <> isnull(v.ColumnName, ''))160      BEGIN161        UPDATE DialogTable162        SET PKName2 = v.ColumnName,163        XDateUpdated = @Xdate,164        XUserUpdated = @XUser165        FROM DialogTable t166          WITH(readpast)167        JOIN dbo.QBM_FTPrimaryKeyInfo_BTVP() v168          ON t.UID_DialogTable = @UID_DialogTable AND v.UID_DialogTable = @UID_DialogTable AND v.IsPKName2 = 1169        WHERE170          isnull(t.PKName2,171        '') <> isnull(v.ColumnName,172        '')173      END174      GOTO endLabel175    END176    IF @Tabletype IN('R',177    'U')178    BEGIN179      IF EXISTS(180        SELECT TOP 1 1181        FROM dialogtable t182        WITH(readpast)183      JOIN(184      SELECT185        t.UID_DialogTable, t.PKName1, t.PKName2, CASE min(isnull(c.ColumnName, ''))186        WHEN '' THEN187        NULL188      ELSE min(isnull(c.ColumnName, ''))189      END AS PKName1Soll, CASE190      WHEN max(isnull(c.ColumnName, '')) = min(isnull(c.ColumnName, '')) THEN191      NULL192      ELSE max(isnull(c.ColumnName, ''))193      END AS PKName2Soll194      FROM DialogTable t195        WITH(readpast)196      LEFT197      OUTER198      JOIN DialogColumn c199        WITH(readpast)200        ON t.UID_DialogTable = c.UID_DialogTable AND c.IsPKMember = 1201      WHERE202        t.UID_DialogTable = @UID_DialogTable203      GROUP BY t.UID_DialogTable, t.PKName1, t.PKName2) AS x204        ON x.UID_DialogTable = t.UID_DialogTable205      WHERE206        isnull(x.PKName1, '') <> isnull(x.PKName1Soll, '') OR isnull(x.PKName2, '') <> isnull(x.PKName2Soll,207      ''))208      BEGIN209        UPDATE DialogTable210        SET PKName1 = x.PKName1Soll,211        PKName2 = x.PKName2Soll,212        XDateUpdated = @Xdate,213        XUserUpdated = @XUser214        FROM dialogtable t215          WITH(readpast)216        JOIN(217        SELECT218          t.UID_DialogTable,219          t.PKName1,220          t.PKName2,221        CASE min(isnull(c.ColumnName, ''))222          WHEN '' THEN223        NULL224        ELSE min(isnull(c.ColumnName, ''))225        END AS PKName1Soll,226        CASE227          WHEN max(isnull(c.ColumnName, '')) = min(isnull(c.ColumnName, '')) THEN228        NULL229        ELSE max(isnull(c.ColumnName, ''))230        END AS PKName2Soll231        FROM DialogTable t232          WITH(readpast)233        LEFT234        OUTER235        JOIN DialogColumn c236          WITH(readpast)237          ON t.UID_DialogTable = c.UID_DialogTable AND c.IsPKMember = 1238        WHERE239          t.UID_DialogTable = @UID_DialogTable240        GROUP BY t.UID_DialogTable,241        t.PKName1,242        t.PKName2) AS x243          ON x.UID_DialogTable = t.UID_DialogTable244        WHERE245          isnull(x.PKName1,246        '') <> isnull(x.PKName1Soll,247        '') OR isnull(x.PKName2,248        '') <> isnull(x.PKName2Soll,249        '')250      END251      GOTO endLabel252    END253    GOTO endlabel musterverarbeitung:254    UPDATE DialogTable255    SET PKName1 = v.ColumnName,256    XDateUpdated = @Xdate,257    XUserUpdated = @XUser258    FROM DialogTable t259    JOIN dbo.QBM_FTPrimaryKeyInfo_BTVP() v260      ON t.UID_DialogTable = v.UID_DialogTable AND v.IsPKName1 = 1261    WHERE262      isnull(t.PKName1,263    '') <> isnull(v.ColumnName,264    '') AND v.TableType NOT IN('U',265    'R') AND t.TableName LIKE @TablePattern266    UPDATE DialogTable267    SET PKName2 = v.ColumnName,268    XDateUpdated = @Xdate,269    XUserUpdated = @XUser270    FROM DialogTable t271    JOIN dbo.QBM_FTPrimaryKeyInfo_BTVP() v272      ON t.UID_DialogTable = v.UID_DialogTable AND v.IsPKName2 = 1273    WHERE274      isnull(t.PKName2,275    '') <> isnull(v.ColumnName,276    '') AND v.TableType NOT IN('U',277    'R') AND t.TableName LIKE @TablePattern278    UPDATE DialogTable279    SET PKName1 = x.PKName1Soll,280    PKName2 = x.PKName2Soll,281    XDateUpdated = @Xdate,282    XUserUpdated = @XUser283    FROM dialogtable t284    JOIN(285    SELECT286      t.UID_DialogTable,287      t.PKName1,288      t.PKName2,289    CASE min(isnull(c.ColumnName, ''))290      WHEN '' THEN291    NULL292    ELSE min(isnull(c.ColumnName, ''))293    END AS PKName1Soll,294    CASE295      WHEN max(isnull(c.ColumnName, '')) = min(isnull(c.ColumnName, '')) THEN296    NULL297    ELSE max(isnull(c.ColumnName, ''))298    END AS PKName2Soll299    FROM DialogTable t300      WITH(readpast)301    LEFT302    OUTER303    JOIN DialogColumn c304      WITH(readpast)305      ON t.UID_DialogTable = c.UID_DialogTable AND c.IsPKMember = 1306    WHERE307      t.TableType IN('R', 'U') AND t.TableName LIKE @TablePattern308    GROUP BY t.UID_DialogTable,309    t.PKName1,310    t.PKName2) AS x311      ON x.UID_DialogTable = t.UID_DialogTable312    WHERE313      isnull(x.PKName1,314    '') <> isnull(x.PKName1Soll,315    '') OR isnull(x.PKName2,316    '') <> isnull(x.PKName2Soll,317    '')318  END TRY319  BEGIN CATCH320    EXEC QBM_PSessionErrorAdd DEFAULT321    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()322    RAISERROR(@Rethrow,323    18,324    1)325      WITH NOWAIT326  END CATCH327  endLabel:328  RETURN329END
Open raw exported source
SQL ยท Raw52 lines
1   create   procedure QBM_PDialogTablePKNameCorrect (@TablePattern varchar(64) = '%' ) as begin declare @UID_DialogTable varchar(38) declare @tablename2 varchar(30) declare @Tabletype varchar(1) declare @PK1Name varchar(30) declare @PK2Name varchar(30) declare @XUser nvarchar(64) = object_name(@@procid3) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY if @TablePattern like '%[%]%' begin  goto musterverarbeitung  end  select top 1 @UID_DialogTable4 = t.UID_DialogTable , @Tabletype = t.TableType , @tablename = t.TableName  , @PK1Name = isnull(t.PKName1, '') , @PK2Name = isnull(t.PKName2, '') from 5DialogTable t with (readpast) where t.TableName = @TablePattern if @Tabletype in ('B', 'T') begin  if exists (select top 1 1 from DialogTable t with (readpast6) join dbo.QBM_FTPrimaryKeyInfo_BT() v on t.UID_DialogTable = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName1 = 1 where isnull7(t.PKName1, '') <> isnull(v.ColumnName, '') ) begin  update DialogTable set PKName1 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser from8 DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BT() v on t.UID_DialogTable = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and9 v.IsPKName1 = 1 where isnull(t.PKName1, '') <> isnull(v.ColumnName, '') end if exists (select top 1 1 from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BT10() v on t.UID_DialogTable = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName2 = 1 where isnull(t.PKName2, '') <> isnull(v.ColumnName11, '') ) begin  update DialogTable set PKName2 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BT12() v on t.UID_DialogTable = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName2 = 1 where isnull(t.PKName2, '') <> isnull(v.ColumnName13, '') end goto endLabel end  if @Tabletype in ('V') begin  if exists (select top 1 1 from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTV14() v on t.UID_DialogTable = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName1 = 1 where isnull(t.PKName1, '') <> isnull(v.ColumnName15, '') ) begin  update DialogTable set PKName1 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTV16() v on t.UID_DialogTable = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName1 = 1 where isnull(t.PKName1, '') <> isnull(v.ColumnName17, '') end if exists (select top 1 1 from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTV() v on t.UID_DialogTable = @UID_DialogTable and18 v.UID_DialogTable = @UID_DialogTable and v.IsPKName2 = 1 where isnull(t.PKName2, '') <> isnull(v.ColumnName, '') ) begin  update DialogTable set PKName219 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTV() v on t.UID_DialogTable20 = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName2 = 1 where isnull(t.PKName2, '') <> isnull(v.ColumnName, '') end goto endLabel21 end  if @Tabletype in ('P') begin  if exists (select top 1 1 from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTVP() v on t.UID_DialogTable22 = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName1 = 1 where isnull(t.PKName1, '') <> isnull(v.ColumnName, '') ) begin  update23 DialogTable set PKName1 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTVP24() v on t.UID_DialogTable = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName1 = 1 where isnull(t.PKName1, '') <> isnull(v.ColumnName25, '') end if exists (select top 1 1 from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTVP() v on t.UID_DialogTable = @UID_DialogTable and26 v.UID_DialogTable = @UID_DialogTable and v.IsPKName2 = 1 where isnull(t.PKName2, '') <> isnull(v.ColumnName, '') ) begin  update DialogTable set PKName227 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogTable t with (readpast) join dbo.QBM_FTPrimaryKeyInfo_BTVP() v on t.UID_DialogTable28 = @UID_DialogTable and v.UID_DialogTable = @UID_DialogTable and v.IsPKName2 = 1 where isnull(t.PKName2, '') <> isnull(v.ColumnName, '') end goto endLabel29 end  if @Tabletype in ('R', 'U') begin if exists(select top 1 1 from dialogtable t with (readpast) join (  select t.UID_DialogTable, t.PKName1, t.PKName230, case min(isnull(c.ColumnName, '')) when '' then null else min(isnull(c.ColumnName, '')) end as PKName1Soll, case when max(isnull(c.ColumnName,'')) = 31min(isnull(c.ColumnName, '')) then null else max(isnull(c.ColumnName,'')) end as PKName2Soll from DialogTable t with (readpast) left outer join DialogColumn32 c with (readpast) on t.UID_DialogTable = c.UID_DialogTable and c.IsPKMember = 1 where t.UID_DialogTable = @UID_DialogTable group by t.UID_DialogTable,33 t.PKName1, t.PKName2 ) as x on x.UID_DialogTable = t.UID_DialogTable where isnull(x.PKName1, '') <> isnull(x.PKName1Soll, '') or isnull(x.PKName2, '')34 <> isnull(x.PKName2Soll, '') ) begin update DialogTable set PKName1 = x.PKName1Soll , PKName2 = x.PKName2Soll , XDateUpdated = @Xdate , XUserUpdated =35 @XUser  from dialogtable t with (readpast) join (  select t.UID_DialogTable, t.PKName1, t.PKName2, case min(isnull(c.ColumnName, '')) when '' then null36 else min(isnull(c.ColumnName, '')) end as PKName1Soll, case when max(isnull(c.ColumnName,'')) = min(isnull(c.ColumnName, '')) then null else max(isnull37(c.ColumnName,'')) end as PKName2Soll from DialogTable t with (readpast) left outer join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable38 and c.IsPKMember = 1 where t.UID_DialogTable = @UID_DialogTable group by t.UID_DialogTable, t.PKName1, t.PKName2 ) as x on x.UID_DialogTable = t.UID_DialogTable39 where isnull(x.PKName1, '') <> isnull(x.PKName1Soll, '') or isnull(x.PKName2, '') <> isnull(x.PKName2Soll, '') end goto endLabel end  goto endlabel musterverarbeitung:40 update DialogTable set PKName1 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t join dbo.QBM_FTPrimaryKeyInfo_BTVP()41 v on t.UID_DialogTable = v.UID_DialogTable and v.IsPKName1 = 1 where isnull(t.PKName1, '') <> isnull(v.ColumnName, '') and v.TableType not in ('U', 'R'42)  and t.TableName like @TablePattern update DialogTable set PKName2 = v.ColumnName , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t43 join dbo.QBM_FTPrimaryKeyInfo_BTVP() v on t.UID_DialogTable = v.UID_DialogTable and v.IsPKName2 = 1 where isnull(t.PKName2, '') <> isnull(v.ColumnName44, '') and v.TableType not in ('U', 'R')  and t.TableName like @TablePattern update DialogTable set PKName1 = x.PKName1Soll , PKName2 = x.PKName2Soll , 45XDateUpdated = @Xdate , XUserUpdated = @XUser  from dialogtable t join (  select t.UID_DialogTable, t.PKName1, t.PKName2, case min(isnull(c.ColumnName,46 '')) when '' then null else min(isnull(c.ColumnName, '')) end as PKName1Soll, case when max(isnull(c.ColumnName,'')) = min(isnull(c.ColumnName, '')) then47 null else max(isnull(c.ColumnName,'')) end as PKName2Soll from DialogTable t with (readpast) left outer join DialogColumn c with (readpast) on t.UID_DialogTable48 = c.UID_DialogTable and c.IsPKMember = 1 where t.TableType in ('R', 'U') and t.TableName like @TablePattern group by t.UID_DialogTable, t.PKName1, t.PKName249 ) as x on x.UID_DialogTable = t.UID_DialogTable where isnull(x.PKName1, '') <> isnull(x.PKName1Soll, '') or isnull(x.PKName2, '') <> isnull(x.PKName2Soll50, '')  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18,51 1) WITH NOWAIT END CATCH endLabel: return end 52