Registered Stored Procedures are procedures that are created directly within the Selligent Configuration. These Registered procedures are available for selection elsewhere in the Selligent tools
- in SQL tasks
- for Dataload tasks
- in Data export tasks
- in the Stored Procedure component in a journey
Stored Procedures created directly on the SQLserver database are not available within the Selligent tools. The main reason for introducing registered Stored Procedures is to follow up on who makes changes to these procedures and when. Change logging is provided.
NOTE: The user needs explicit rights to create registered Stored Procedures.
Technical note: When creating a registered Stored Procedure, do not put a GO command at the end. The creation of the procedure fails if you do so.
When accessing the 'Registered Stored Procedures' section, a list of all existing Stored Procedures is displayed. Selecting a procedure from the list displays the change log in the lower section:
The toolbar provides following functionality:
- New Stored Procedure
- Deleted Stored Procedure
- Properties
Creating a Stored Procedure
When creating a new Stored Procedure, following properties need to be defined:
- Name & Description:
- Script: in the Script section enter the SQL code to execute.
The 'Group rights' tab is used to define what user groups have access to this Stored Procedure. Note that granting access rights is equally possible from the 'Asset rights' section in the Configuration.
Example Stored Procedure:
Update of a dedicated coupon table, to indicate that the coupon has been used for a defined contact:
@COUPON NVARCHAR(25), @UID INT
AS
BEGIN
SET NOCOUNT ON;
update DATA_COUPONS
set COUPON_USED='Y',USERID=@UID,USED_DT=GETDATE()
where COUPON_CODE=@COUPON
END
Example 2 Stored Procedure
AS
BEGIN
ALTER TABLE SYNC_RETAIL_ORDERS
ADD USERID INT;
UPDATE SYNC_RETAIL_OPTIN
SET OPTI_REJECTED = CASE WHEN U.ID IS NULL THEN 1 ELSE NULL END,
OPTI_REJECTED_REASON = CASE WHEN U.ID IS NULL THEN 'USER NOT FOUND' ELSE NULL END,
USERID = U.ID
FROM SYNC_RETAIL_OPTIN R
LEFT JOIN USERS_RETAIL U with (nolock) ON R.CUSTOMER_NUMBER = U.CUSTOMER_NUMBER
MERGE dbo.DATA_RETAIL_OPTIN AS T
USING dbo.SYNC_RETAIL_OPTIN AS S
ON (T.USERID = S.USERID) AND OPTI_REJECTED IS NULL
WHEN MATCHED THEN
UPDATE SET
T.USERID = S.USERID,
T.OPTIN_NEWSLETTER = S.OPTIN_NEWSLETTER,
T.OPTIN_NEWSLETTER_DT = S.OPTIN_NEWSLETTER_DT,
T.OPTIN_NEWSFLASH = S.OPTIN_NEWSFLASH,
T.OPTIN_NEWSFLASH_DT = S.OPTIN_NEWSFLASH_DT,
T.MODIFIED_DT = GETDATE()
WHEN NOT MATCHED THEN
INSERT (USERID, OPTIN_NEWSLETTER, OPTIN_NEWSLETTER_DT, OPTIN_NEWSFLASH, OPTIN_NEWSFLASH_DT, CREATED_DT)
VALUES (S.USERID, S.OPTIN_NEWSLETTER, S.OPTIN_NEWSLETTER_DT, S.OPTIN_NEWSFLASH, S.OPTIN_NEWSFLASH_DT, GETDATE())
;END
