un package qui s'exécute en boucle.... comment faire?
1 réponse
Fernand St-Georges
Actuellement, cette procédure s'exécute bien, mais elle ne met à jour qu'une
seule occurence. Comment dois-je réécrire cette procédure afin qu'elle
mette à jour toutes les occurences de la base de données?
Merci
declare @tlongtextvar as varchar (8000),
@tspecK as int
select @tlongtextvar = dbo.tLongTxt.tLongTxt,
@tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Sylvain Lafontaine
Normalement, vous devriez utiliser un Curseur pour parcourir une requête de sélection ligne par ligne; regardez dans l'aide en ligne ou dans n'importe quel bon livre sur SQL-Server.
Il est peut-être possible de fusionner vos deux requêtes Select et Update en une seule et de modifier la table en une seule opération mais comme vous n'avez pas été désireux de la simplifier un tant soi peu, j'imagine qu'il n'y aura pas grand monde ici qui vont chercher à comparer les deux.
-- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF
"Fernand St-Georges" <Fernand wrote in message news:QBOng.17570$
Actuellement, cette procédure s'exécute bien, mais elle ne met à jour qu'une seule occurence. Comment dois-je réécrire cette procédure afin qu'elle mette à jour toutes les occurences de la base de données?
Merci
declare @tlongtextvar as varchar (8000), @tspecK as int
select @tlongtextvar = dbo.tLongTxt.tLongTxt, @tspecK = dbo.tLongTxt.k FROM dbo.rTable INNER JOIN dbo.tLongTxt ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND (dbo.tLongTxt.tSpecConc = 22500) AND (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND ( dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like '%>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @tlongtextvar + '</EntityDescription>' FROM dbo.rTable INNER JOIN dbo.tLongTxt ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND (dbo.tLongTxt.tSpecConc = 22500) AND (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND (dbo.tLongTxt.K = @tspecK) AND ( dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like '%>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
Normalement, vous devriez utiliser un Curseur pour parcourir une requête de
sélection ligne par ligne; regardez dans l'aide en ligne ou dans n'importe
quel bon livre sur SQL-Server.
Il est peut-être possible de fusionner vos deux requêtes Select et Update en
une seule et de modifier la table en une seule opération mais comme vous
n'avez pas été désireux de la simplifier un tant soi peu, j'imagine qu'il
n'y aura pas grand monde ici qui vont chercher à comparer les deux.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Fernand St-Georges" <Fernand St-Georges@videotron.ca> wrote in message
news:QBOng.17570$sM4.70229@weber.videotron.net...
Actuellement, cette procédure s'exécute bien, mais elle ne met à jour
qu'une seule occurence. Comment dois-je réécrire cette procédure afin
qu'elle mette à jour toutes les occurences de la base de données?
Merci
declare @tlongtextvar as varchar (8000),
@tspecK as int
select @tlongtextvar = dbo.tLongTxt.tLongTxt,
@tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
Normalement, vous devriez utiliser un Curseur pour parcourir une requête de sélection ligne par ligne; regardez dans l'aide en ligne ou dans n'importe quel bon livre sur SQL-Server.
Il est peut-être possible de fusionner vos deux requêtes Select et Update en une seule et de modifier la table en une seule opération mais comme vous n'avez pas été désireux de la simplifier un tant soi peu, j'imagine qu'il n'y aura pas grand monde ici qui vont chercher à comparer les deux.
-- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF
"Fernand St-Georges" <Fernand wrote in message news:QBOng.17570$
Actuellement, cette procédure s'exécute bien, mais elle ne met à jour qu'une seule occurence. Comment dois-je réécrire cette procédure afin qu'elle mette à jour toutes les occurences de la base de données?
Merci
declare @tlongtextvar as varchar (8000), @tspecK as int
select @tlongtextvar = dbo.tLongTxt.tLongTxt, @tspecK = dbo.tLongTxt.k FROM dbo.rTable INNER JOIN dbo.tLongTxt ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND (dbo.tLongTxt.tSpecConc = 22500) AND (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND ( dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like '%>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @tlongtextvar + '</EntityDescription>' FROM dbo.rTable INNER JOIN dbo.tLongTxt ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND (dbo.tLongTxt.tSpecConc = 22500) AND (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND (dbo.tLongTxt.K = @tspecK) AND ( dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like '%>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based On IBF (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
