Advertisement
DataCCIW

Get/Save Seasonal Address Procedure

Dec 23rd, 2019
647
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.46 KB | None | 0 0
  1. CREATE PROCEDURE [dbo].[cust_CCIW_get_save_seasonal_address]
  2. @OrganizationID INT,
  3. @Guid UNIQUEIDENTIFIER,
  4. @FromMonthDay VARCHAR(4),
  5. @ToMonthDay VARCHAR(4),
  6. @AddressLUID INT = -1
  7.  
  8. AS
  9. BEGIN
  10.  
  11.     IF @ToMonthDay != ''
  12.     AND @FromMonthDay != ''
  13.     AND LEN(@FromMonthDay) = 4
  14.     AND LEN(@ToMonthDay) = 4
  15.     BEGIN
  16.         UPDATE core_person_address
  17.         SET from_month_day = @FromMonthDay,
  18.         to_month_day = @ToMonthDay
  19.         FROM core_person P
  20.         JOIN core_family_member M1 ON M1.person_id = P.person_id
  21.         JOIN core_family_member M2 ON M1.family_id = M2.family_id
  22.         JOIN core_person_address A2 ON A2.person_id = M2.person_id
  23.         WHERE P.guid = @Guid
  24.         AND A2.address_type_luid = @AddressLUID
  25.     END
  26.    
  27.     IF (@ToMonthDay = '' AND @FromMonthDay != '')
  28.     OR (@ToMonthDay != '' AND @FromMonthDay = '')
  29.     BEGIN
  30.          SELECT 'Please input both To and From dates' AS 'Error Message'
  31.     END
  32.     ELSE IF (@ToMonthDay = '' AND @FromMonthDay = '')
  33.     BEGIN
  34.     SELECT CPA.* FROM core_person P
  35.        JOIN core_person_address CPA ON P.person_id = CPA.person_id
  36.          WHERE P.guid = @Guid
  37.          AND CPA.address_type_luid = @AddressLUID
  38.     END
  39.     ELSE IF (LEN(@FromMonthDay) != 4 OR LEN(@ToMonthDay) != 4)
  40.     BEGIN
  41.          SELECT 'Unsupported date format' AS 'Error Message'
  42.     END
  43.     ELSE
  44.     BEGIN
  45.     SELECT CPA.* FROM core_person P
  46.        JOIN core_person_address CPA ON P.person_id = CPA.person_id
  47.          WHERE P.guid = @Guid
  48.          AND CPA.address_type_luid = @AddressLUID
  49.     END
  50.      
  51.  
  52. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement