Creating and Calling T-SQL Stored Procedures

trellispark allows you to add T-SQL Stored procedures to your DAS-RSS database so that you can place workflow automation as close as possible to your record data.

Session Validation

The vast majority of T-SQL Stored Procedures in the DAS-RSS database use a @SessionGUID/@UserGUID matching pair to validate that the request has been submitted by a user who has a current valid session.

Such T-SQL Stored Procedures begin with the following code fragment that performs the session validation.

DECLARE @ReturnStatus AS INTEGER
EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
IF @ReturnStatus > 0
BEGIN
    EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'T-SQL Stored Procedure Name - Invalid session', 1, NULL
    RAISERROR ('T-SQL Stored Procedure Name - Invalid session.', 16, 1)
    RETURN 100 + @ReturnStatus
END

User Access Validation

Assuming that the user’s session is valid, a second check is usually done to verify that the user has access to the record upon which they wish to act. The instance is usually specified by either the @CurrentInstanceGUID or @InstanceGUID parameter depending upon the calling scenario.

EXECUTE @ReturnStatus = dbo.GI_ValidateReadAccess @SessionGUID, @UserGUID, @InstanceGUID
IF @ReturnStatus <> 0
BEGIN		
    -- Log and raise the error...
    EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'T-SQL Stored Procedure Name - The user is not able to access this instance data', 1, @ErrorMessage
    RAISERROR ('T-SQL Stored Procedure Name - The user is not able to access this instance data.', 16, 1)
    RETURN 200 + @ReturnStatus
END

Error Handling

All the T-SQL stored procedures should include a standard error handler using the TRY/CATCH structure to catch unknown errors. Any errors detected should be added to the database’s EventLog table using the dbo.GI_LogEvent stored procedure.

BEGIN TRY

    -- your code here...
    RETURN 0

END TRY
BEGIN CATCH
    DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()

    EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'T-SQL Stored Procedure Name - Unknown error', 1, @ErrorMessage
    RAISERROR ('T-SQL Stored Procedure Name - Unknown error', 16, 1)
    RETURN 1
END CATCH

Parameter Specifications

Execute a command from the user experience, these stored procedures do not return a value.

CREATE OR ALTER PROCEDURE [dbo].[CommandTarget] 
(
    @SessionGUID AS UNIQUEIDENTIFIER, 
    @UserGUID AS UNIQUEIDENTIFIER, 
    @CurrentInstanceGUID AS UNIQUEIDENTIFIER, 
    @TargetInstanceGUID AS UNIQUEIDENTIFIER
)

To return a dataset for a Childlist that will contain columns for at least the InstanceGUID and InstanceName.

CREATE OR ALTER PROCEDURE [dbo].[GridRowSelection] 
(
    @SessionGUID AS UNIQUEIDENTIFIER, 
    @UserGUID AS UNIQUEIDENTIFIER, 
    @InstanceGUID AS UNIQUEIDENTIFIER
)

To return a dataset with an optional target GUID to filter the rows. The dataset should contain columns for at least the InstanceGUID and InstanceName.

CREATE OR ALTER PROCEDURE [dbo].[ReturnGeneralDataset]
(
    @SessionGUID AS UNIQUEIDENTIFIER,
    @UserGUID AS UNIQUEIDENTIFIER,
    @CurrentInstanceGUID AS UNIQUEIDENTIFIER,
    @TargetInstanceGUID AS UNIQUEIDENTIFIER
)

To return a dataset with an input filter to restrict row selection. This is used when a Childlist is being restricted using a Seach UX Component. The current search parameter values are converted to XML and injected using the @InputString.

CREATE OR ALTER PROCEDURE [dbo].[ReturnFilteredDataset]
(
    @SessionGUID AS UNIQUEIDENTIFIER,
    @UserGUID AS UNIQUEIDENTIFIER,
    @CurrentInstanceGUID AS UNIQUEIDENTIFIER,
    @TargetInstanceGUID AS UNIQUEIDENTIFIER,
    @InputString AS VARCHAR(MAX)
)

To read a result from the database.

CREATE OR ALTER PROCEDURE [dbo].[GetResult]
(
    @SessionGUID AS UNIQUEIDENTIFIER,
    @UserGUID AS UNIQUEIDENTIFIER,
    @CurrentInstanceGUID AS UNIQUEIDENTIFIER,
    @TargetInstanceGUID AS UNIQUEIDENTIFIER,
    @Result AS VARCHAR(MAX) OUTPUT
)

To read a result from the database using a filter to restrict the return value.

CREATE OR ALTER PROCEDURE [dbo].[GetFilteredResult] 
(
    @SessionGUID AS UNIQUEIDENTIFIER,
    @UserGUID AS UNIQUEIDENTIFIER,
    @CurrentInstanceGUID AS UNIQUEIDENTIFIER,
    @TargetInstanceGUID AS UNIQUEIDENTIFIER,
    @Filter AS VARCHAR(100),
    @Result AS VARCHAR(MAX) OUTPUT
)