T-SQL stored procedure add ALTER TABLE function

Question

I have added these foreign keys in a table using an alter table function.

I would like to add these as one stored procedure so when I execute the stored procedure, it will run and add these foreign keys in my table. Any help? Thanks

ALTER TABLE [child] 
    ADD FOREIGN KEY ([mother_father_caregiver_id]) 
        REFERENCES [education] ([education_id])

ALTER TABLE [child] 
    ADD FOREIGN KEY ([education_id]) 
        REFERENCES [caregiver] ([mother_father_caregiver_id])

ALTER TABLE [child] 
    ADD FOREIGN KEY ([household_id]) 
        REFERENCES [household] ([household_id])

ALTER TABLE [household] 
    ADD FOREIGN KEY ([householdage_id]) 
        REFERENCES [householdage] ([householdage_id])

ALTER TABLE [household] 
    ADD FOREIGN KEY ([livestock_id]) 
        REFERENCES [livestock] ([livestock_id])

ALTER TABLE [household] 
    ADD FOREIGN KEY ([credit_id]) 
        REFERENCES [credit] ([credit_id])

ALTER TABLE [household] 
    ADD FOREIGN KEY ([shock_id]) 
        REFERENCES [householdshocks] ([shock_id])

It runs fine this way, but I need to make it to a stored procedure. I tried this and it does not work, I get an error. — Harris A, Mar 28 '20 at 20:19
CREATE PROCEDURE [FOREIGN_KEYS] BEGIN ALTER TABLE [child] ADD FOREIGN KEY ([mother_father_caregiver_id]) REFERENCES [education] ([education_id]) ALTER TABLE [child] ADD FOREIGN KEY ([education_id]) REFERENCES [caregiver] ([mother_father_caregiver_id]) ALTER TABLE [child] ADD FOREIGN KEY ([household_id]) REFERENCES [household] ([household_id]) END — Harris A, Mar 28 '20 at 20:22
Why would you want these in an SP? Once they are run the SP is pointless. — Thom A, Mar 28 '20 at 21:52

score 0 · Answer 1 · edited Mar 28 '20 at 22:24

I ended up figuring it out myself, here is the answer.

CREATE PROCEDURE spForeignKeys
AS
BEGIN 
    ALTER TABLE [child] ADD FOREIGN KEY  ([household_id]) REFERENCES [household] ([household_id])

    ALTER TABLE [child] ADD FOREIGN KEY  ([panel_id]) REFERENCES [panel] ([panel_id])

    ALTER TABLE [child] ADD FOREIGN KEY  ([childcharacteristics_id]) REFERENCES [childcharacteristics] ([childcharacteristics_id])
    ALTER TABLE [child] ADD FOREIGN KEY  ([anthropometric_id]) REFERENCES [anthropometric] (anthropometric_id)
    ALTER TABLE [child] ADD FOREIGN KEY  ([immunisation_id]) REFERENCES [birthimmunisation] ([immunisation_id])
    ALTER TABLE [child] ADD FOREIGN KEY  ([health_id]) REFERENCES [childhealth] ([health_id])
    ALTER TABLE [child] ADD FOREIGN KEY  ([timeuse_id]) REFERENCES [timeuse] ([timeuse_id])
    ALTER TABLE [child] ADD FOREIGN KEY  ([habits_id]) REFERENCES [habits] ([habits_id])
    ALTER TABLE [child] ADD FOREIGN KEY  ([mother_father_caregiver_id]) REFERENCES [education] ([education_id])
    ALTER TABLE [child] ADD FOREIGN KEY  ([education_id]) REFERENCES [caregiver] ([mother_father_caregiver_id])

    ALTER TABLE [household] ADD FOREIGN KEY  ([householdage_id]) REFERENCES [householdage] ([householdage_id])
    ALTER TABLE [household] ADD FOREIGN KEY  ([livestock_id]) REFERENCES [livestock] ([livestock_id])
    ALTER TABLE [household] ADD FOREIGN KEY  ([credit_id]) REFERENCES [credit] ([credit_id])
    ALTER TABLE [household] ADD FOREIGN KEY  ([shock_id]) REFERENCES [householdshocks] ([shock_id])
    ALTER TABLE [household] ADD FOREIGN KEY  ([wealth_id]) REFERENCES [wealthindex] ([wealth_id])
END;
EXECUTE spForeignKeys;

T-SQL stored procedure add ALTER TABLE function

1 Answers1