catprotection
asked on
conditional declaration of local variables in stored procedure
I have a stored procedure that i'm using to either INSERT or UPDATE a record and its dependants. It will do the UPDATE ok but fails the INSERT because I am declaring 2 variables @cat_id1 and @cat_id2 for the whole proc. If it is an update then the webpage can pass the 2 ID's to the Stored Proc ok, for INSERT as there are no primary Keys I cannot pass them in...
So how can i conditionally declare my 2 variables?
-- ========================== ========== =========
-- Create procedure to add / update contact
-- Author: Simon Martin 27/09/04
-- ========================== ========== =========
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'usp_AddUpdateContact'
AND type = 'P')
DROP PROCEDURE usp_AddUpdateContact
GO
CREATE PROCEDURE usp_AddUpdateContact
@first_name varchar(50),
@last_name varchar(50),
@honorific_id int,
@sex_id int,
@address1 varchar(50),
@address2 varchar(50),
@street varchar(50),
@town varchar (50),
@county varchar (50),
@postcode char(8),
@region int,
@phone varchar(20),
@mobile varchar(20),
@email varchar(50),
@notes text,
@cat_id1 int,
@cat_id2 int,
@cat_name1 varchar(50),
@cat_name2 varchar(50),
@cat_sex1 int,
@cat_sex2 int,
@age_id1 int,
@age_id2 int,
@vet_id int
AS
-- Declare and initialise local variables
DECLARE @action char(1), -- 'I' = Insert; 'U' = Update
@contact_id int
-- Determine action taken to cause trigger
IF EXISTS (SELECT c.contact_id FROM tbl_contact c WHERE c.postcode = @postcode AND c.address1 = @address1)
SET @action = 'U'
ELSE
SET @action = 'I'
IF EXISTS (SELECT c.contact_id FROM tbl_contact c WHERE c.postcode = @postcode AND c.address1 = @address1)
SET @contact_id = (SELECT c.contact_id FROM tbl_contact c WHERE c.postcode = @postcode AND c.address1 = @address1)
-- Acutal trigger logic starts here
IF @action = 'U'
BEGIN
UPDATE tbl_contact
SET first_name = @first_name, last_name = @last_name, honorific_id = @honorific_id, sex_id = @sex_id, address1 = @address1, address2 = @address2, street = @street, town = @town, county = @county, postcode = @postcode, region = @region, phone = @phone, mobile = @mobile, email = @email, notes = @notes
WHERE contact_id = @contact_id
-- Update linked table with cats details
UPDATE tbl_cat
SET cat_name = @cat_name1, cat_sex = @cat_sex1, age_id = @age_id1, contact_id = @contact_id
WHERE cat_id = @cat_id1
UPDATE tbl_cat
SET cat_name = @cat_name2, cat_sex = @cat_sex2, age_id = @age_id2, contact_id = @contact_id
WHERE cat_id = @cat_id2
END
ELSE
IF @action = 'I'
BEGIN
INSERT INTO tbl_contact (first_name, last_name, honorific_id, sex_id, address1, address2, street, town, county, postcode, region, phone, mobile, email, notes)
VALUES (@first_name, @last_name, @honorific_id, @sex_id, @address1, @address2, @street, @town, @county, @postcode, @region, @phone, @mobile, @email, @notes)
-- Pick up new id to create child relationship
DECLARE @Newid int;
SET @Newid = (SELECT @@IDENTITY)
-- Create details for cat1 and voucher
INSERT INTO tbl_cat (cat_name, cat_sex, age_id, contact_id)
VALUES (@cat_name1, @cat_sex1, @age_id1, @Newid)
DECLARE @NewCat1 int;
SET @NewCat1 = (SELECT @@IDENTITY)
INSERT INTO tbl_voucher (region_id, contact_id, cat_id, vet_id)
VALUES (@region, @Newid, @NewCat1, @vet_id)
-- Create details for cat2 and voucher
INSERT INTO tbl_cat (cat_name, cat_sex, age_id, contact_id)
VALUES (@cat_name2, @cat_sex2, @age_id2, @Newid)
DECLARE @NewCat2 int;
SET @NewCat2 = (SELECT @@IDENTITY)
INSERT INTO tbl_voucher (region_id, contact_id, cat_id, vet_id)
VALUES (@region, @Newid, @NewCat2, @vet_id)
END
So how can i conditionally declare my 2 variables?
-- ==========================
-- Create procedure to add / update contact
-- Author: Simon Martin 27/09/04
-- ==========================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'usp_AddUpdateContact'
AND type = 'P')
DROP PROCEDURE usp_AddUpdateContact
GO
CREATE PROCEDURE usp_AddUpdateContact
@first_name varchar(50),
@last_name varchar(50),
@honorific_id int,
@sex_id int,
@address1 varchar(50),
@address2 varchar(50),
@street varchar(50),
@town varchar (50),
@county varchar (50),
@postcode char(8),
@region int,
@phone varchar(20),
@mobile varchar(20),
@email varchar(50),
@notes text,
@cat_id1 int,
@cat_id2 int,
@cat_name1 varchar(50),
@cat_name2 varchar(50),
@cat_sex1 int,
@cat_sex2 int,
@age_id1 int,
@age_id2 int,
@vet_id int
AS
-- Declare and initialise local variables
DECLARE @action char(1), -- 'I' = Insert; 'U' = Update
@contact_id int
-- Determine action taken to cause trigger
IF EXISTS (SELECT c.contact_id FROM tbl_contact c WHERE c.postcode = @postcode AND c.address1 = @address1)
SET @action = 'U'
ELSE
SET @action = 'I'
IF EXISTS (SELECT c.contact_id FROM tbl_contact c WHERE c.postcode = @postcode AND c.address1 = @address1)
SET @contact_id = (SELECT c.contact_id FROM tbl_contact c WHERE c.postcode = @postcode AND c.address1 = @address1)
-- Acutal trigger logic starts here
IF @action = 'U'
BEGIN
UPDATE tbl_contact
SET first_name = @first_name, last_name = @last_name, honorific_id = @honorific_id, sex_id = @sex_id, address1 = @address1, address2 = @address2, street = @street, town = @town, county = @county, postcode = @postcode, region = @region, phone = @phone, mobile = @mobile, email = @email, notes = @notes
WHERE contact_id = @contact_id
-- Update linked table with cats details
UPDATE tbl_cat
SET cat_name = @cat_name1, cat_sex = @cat_sex1, age_id = @age_id1, contact_id = @contact_id
WHERE cat_id = @cat_id1
UPDATE tbl_cat
SET cat_name = @cat_name2, cat_sex = @cat_sex2, age_id = @age_id2, contact_id = @contact_id
WHERE cat_id = @cat_id2
END
ELSE
IF @action = 'I'
BEGIN
INSERT INTO tbl_contact (first_name, last_name, honorific_id, sex_id, address1, address2, street, town, county, postcode, region, phone, mobile, email, notes)
VALUES (@first_name, @last_name, @honorific_id, @sex_id, @address1, @address2, @street, @town, @county, @postcode, @region, @phone, @mobile, @email, @notes)
-- Pick up new id to create child relationship
DECLARE @Newid int;
SET @Newid = (SELECT @@IDENTITY)
-- Create details for cat1 and voucher
INSERT INTO tbl_cat (cat_name, cat_sex, age_id, contact_id)
VALUES (@cat_name1, @cat_sex1, @age_id1, @Newid)
DECLARE @NewCat1 int;
SET @NewCat1 = (SELECT @@IDENTITY)
INSERT INTO tbl_voucher (region_id, contact_id, cat_id, vet_id)
VALUES (@region, @Newid, @NewCat1, @vet_id)
-- Create details for cat2 and voucher
INSERT INTO tbl_cat (cat_name, cat_sex, age_id, contact_id)
VALUES (@cat_name2, @cat_sex2, @age_id2, @Newid)
DECLARE @NewCat2 int;
SET @NewCat2 = (SELECT @@IDENTITY)
INSERT INTO tbl_voucher (region_id, contact_id, cat_id, vet_id)
VALUES (@region, @Newid, @NewCat2, @vet_id)
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DOH!