Writing large SQL scripts advice

[Shicky]

Long story short I have a rather huge SQL script to write...or it seems unavoidable to me anyway. Basically when dealing with a support item it was discovered a fix had skipped out a bunch of records.

Now in order to correct this I need to write an update script to fix all the information that was missed. The problem is this change will touch on over 20 tables. Do I really have to write some huge ass script, declare a bunch of variables at the top and update each table one by one?

Is there a better way to do this?

 

[Shicky]

Sorry I'm a bit new to this so just trying to get my head around linking everything up.

At the moment I have a normal query - SELECT FROM WHERE which basically finds about 2000 records that I need to update which link across several tables.

Can someone tell me how I can link this simple query to something else so I can basically execute several stored procedures, all in the same script? But only affecting the records returned by my simple query?

Apologies, that probably sounds as clear as mud!

 

[Shicky]

So below is the select query. The rows that I get from this, I will need to use information from to do several inserts/updates into other tables. Stored procedures exist for some so I would like to use those where I can, does this sound remotely plausible?

SELECT [MembershipTermID]
  ,[MemberStatusProgKey]
  ,[StartDate]
  ,[EndDate]
  ,[AdditionalDiscount]
  ,[EntryDateTime]
  ,[UpdateDateTime]
  ,[MembershipID]
  ,[AgentID]
  ,[PlanVersionID]
  ,[ForceThroughReference]
  ,[IsForceThrough]
  ,[NextTermPrePaid]
  ,[IsBillingMonthly]
  ,[CICSMEMBERNUM]
  ,[CICSHISTORY]
  ,[TMPSeqNoColumn]
  ,[LastPaymentDate]
  ,[PaidToDate]
  ,[IsIndeterminate]
  ,DATEDIFF(MONTH, PaidToDate, GETDATE()) as MonthsDifference
  ,dbo.FullMonthsSeparation (PaidToDate, GETDATE())
FROM [Apollo].[dbo].[MembershipTerm] 
WHERE MemberStatusProgKey='DORMANT' 
AND IsBillingMonthly=1 
AND dbo.FullMonthsSeparation (PaidToDate, GETDATE()) >= 2

 

[Shicky]

Frankly my mind is just boggled as to what the structure of this should be.

Do I run my select query first, then execute a bunch of stored procedures? Will this only affect the rows that have been pulled from the select query?

A side question would be is it possible to get values from a previously executed stored procedure?

Such as:

EXEC spCancellationDetailInsert 'SYSTEM', 0, 0.0, [EndDate], @TodaysDate, @TodaysDate,'CANC_DORMANT'

EXEC spMembershipTermCancellationDetailInsert 'SYSTEM', 0, <CancellationId>???, [MembershipTermID]

where cancellationID would have been created from the previously executed stored procedure?

Another is the ones in [] are from the select query itself, is that the correct way to reference them? Going to have to go through some counter so it executes all these stored procedures for each row

Feel a bit out of my depth!

 

[Shicky]

Using SQL Server 2008 I believe and using SQL Server Management studio to write these queries etc. Not too sure what you mean by server side coding so I'm guessing the answer is no