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
catprotectionAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You can make any parameter optional by giving it a default value in the parm declaration:

CREATE PROC ...
    @...,
    @cat_id1 int = NULL,
    @cat_id2 int = NULL,
    @...,
0
 
catprotectionAuthor Commented:
One word...

DOH!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.