Stored Procedure Optimise

• Brendan Collins

  SSC Eights!

  Points: 822

  More actions

  May 5, 2006 at 6:16 am

  #117542

  Hi

  I have the below stored procedure, which should function;-), but I was wondering if there was a more efficient way of putting a value into the @addrLn1 variable. If no address has been entered then I am checking in the 4 If statements for the AddressClassificationId value and then if the corresponding ADDR_LN_1 field isnt blank then set it to @addrLn1.

  Thanks, any help appreciated.

  Brendan

  CREATE PROCEDURE InsAddress

  (@casid varchar(18), @clttype bit, @add_1 varchar(255), @add_2 varchar(70), @add_3 varchar(70), @add_4 varchar(70), @add_5 varchar(70), @cpostcode varchar(18))

  AS

  Declare @entityid int, @lobid int, @addrLn1 varchar(255)

  -- General Check CASID LOB

  SELECT @entityid=EntityId FROM [COPER].[dbo].Entity WHERE Casid =@casid

  IF @clttype =0 --External Only

  Begin

  --Check Address has been entered

     IF (Len(@add_1) < 1)
     Begin
      Select @addrLn1 = ADDR_LN_1
      From EntityAddress
      Where AddressClassificationId = 2
      And AddressId In (Select AddressId From EntityLOBAddress Where EntityId = @entityid And LOBId = @lobid)

      If Len(@addrLn1) < 1
      Begin
       Select @addrLn1 = ADDR_LN_1
       From EntityAddress
       Where AddressClassificationId = 4
       And AddressId In (Select AddressId From EntityLOBAddress Where EntityId = @entityid And LOBId = @lobid)
      End

      If Len(@addrLn1) < 1
      Begin
       Select @addrLn1 = ADDR_LN_1
       From EntityAddress
       Where AddressClassificationId = 3
       And AddressId In (Select AddressId From EntityLOBAddress Where EntityId = @entityid And LOBId = @lobid)
      End

      If Len(@addrLn1) < 1
      Begin
       Select @addrLn1 = ADDR_LN_1
       From EntityAddress
       Where AddressClassificationId = 1
       And AddressId In (Select AddressId From EntityLOBAddress Where EntityId = @entityid And LOBId = @lobid)
      End

      If Len(@addrLn1) < 1
      Begin
       Set @addrLn1 = 'To be changed'
      End
     End

  INSERT INTO [TestDb].[dbo].[EntityAddress]
  ([AddressClassificationId],[ADDR_LN_1],[ADDR_LN_2],[ADDR_LN_3],[ADDR_LN_4],[ADDR_LN_5],[POST_CODE])
  VALUES(4, @addrLn1, @add_2, @add_3, @add_4, @add_5, @cpostcode)
  End
  GO

• Eddie Wuerch

  SSChampion

  Points: 12504

  More actions

  May 7, 2006 at 8:10 pm

  #636443

  Here's something to consider:  Order the EntityAddress table by the order of the classification IDs you wish to check (ID = 2 first, ID = 4 second, etc.) and pull the TOP 1 from the result:

  CREATE PROC dbo.InsAddress(
  @casid 	   varchar(18), 
  @clttype   bit, 
  @add_1     varchar(255), 
  @add_2     varchar(70), 
  @add_3     varchar(70), 
  @add_4     varchar(70), 
  @add_5     varchar(70), 
  @cpostcode varchar(18)
  )
  
  AS
  SET NOCOUNT ON 
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  DECLARE @Sortr table(AddressClassificationId int NOT NULL PRIMARY KEY, SortOrder int NOT NULL)
  INSERT @Sortr (AddressClassificationId, SortOrder)
  -- Build a sort table setting the order of the ClassificationIDs to check
  SELECT 1, 4 UNION SELECT 2, 1 UNION SELECT 3, 3 UNION SELECT 4, 2

  DECLARE @entityid int, @lobid int, @addrLn1 varchar(255)
  
  -- General Check CASID LOB 
  
  SELECT @entityid=EntityId FROM COPER.dbo.Entity WHERE Casid =@casid

  IF @clttype = 0 AND Len(IsNull(@add_1, '')) < 1 
    BEGIN
      IF EXISTS(SELECT * 
                  FROM dbo.EntityAddress    EntAdd INNER JOIN
                       dbo.EntityLOBAddress LOBAdd ON EntAdd.AddressID = LOBAdd.AddressID
                 WHERE LOBAdd.EntityId = @entityid AND LOBAdd.LOBId = @lobid)
        -- Got a hit!  Grab the first occurrance according to the sort order table
        INSERT TestDb.dbo.EntityAddress (AddressClassificationId, ADDR_LN_1, ADDR_LN_2, 
                                         ADDR_LN_3, ADDR_LN_4, ADDR_LN_5,POST_CODE)
        SELECT TOP 1 4, EntAdd.ADDR_LN_1, @add_2, @add_3, 
                     @add_4, @add_5, @cpostcode
          FROM dbo.EntityAddress    EntAdd INNER JOIN
               dbo.EntityLOBAddress LOBAdd ON EntAdd.AddressID = LOBAdd.AddressID INNER JOIN
               @Sortr s ON EntAdd.AddressClassificationID = s.AddressClassificationID
         WHERE LOBAdd.EntityId = @entityid AND LOBAdd.LOBId = @lobid
         ORDER BY s.SortOrder

      ELSE

        -- No hit. Use a default phrase.
        INSERT TestDb.dbo.EntityAddress (AddressClassificationId, ADDR_LN_1, ADDR_LN_2, 
                                         ADDR_LN_3, ADDR_LN_4, ADDR_LN_5,POST_CODE)
        VALUES(4, 'To be changed', @add_2, @add_3, @add_4, @add_5, @cpostcode)

    END
  GO

  -Eddie

   

  Eddie Wuerch
  MCM: SQL