Solved

conditional declaration of local variables in stored procedure

Posted on 2004-10-05
2
318 Views
Last Modified: 2009-12-16
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
0
Comment
Question by:catprotection
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 450 total points
ID: 12228048
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
 

Author Comment

by:catprotection
ID: 12228151
One word...

DOH!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question