version

Guide development

This feature is a beta preview. Undocumented changes may occur.

Developers may create their own guides in addition to the ones that are distributed with Softadmin®. This section refers to creating new guides, not using existing guides for development.

A guide consists of 2 steps. The guide first specifies all parameters it needs as input from guide user in the "Get parameters"-call. Then it is uses this input to generate objects in the "Create objects"-call. It is strongly recommended to use the guide button when creating the SQL for a new guide.

The procedures and functions in SoftadminGuide can be used to generate parameters and create objects. The procedures from this schema must be used to create or alter objects, for example SoftadminGuide.SqlModule_Create.

SQL

SQL Call: Get parameters (mandatory)

The component asks what parameters should be generated for user input.

May modify database: No

Parameters

@Action string

Will be set to 'GetParameters' for the get parameter call.

Resultset: Parameters

A resultset with the columns created by SoftadminGuide.Parameter_MultiControl_Definition.

Table count: repeated exactly once

Row count: zero or more rows

Columns

SQL Call: Create objects (mandatory)

The component wants to create objects.

May use SoftadminGuide.ReturnCode_NoLogging as return value to suppress logging, for example when showing error messages.

May modify database: No

Parameters

@Action string

Will be set to 'CreateObjects' for the create objects call.

@MultiControlParametersTableName string

Table name of the multi-control created for the parameters.

Examples

Main procedure

Responsible for parameter creation and parsing as well as calling any other internal procedures required by the guide.

CREATE OR ALTER PROCEDURE dbo.Grid_Example_Guide
	@Action varchar(300),
	@MultiControlParametersTableName varchar(MAX) = NULL
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;

	DECLARE @ProcedureNameFieldName varchar(MAX) = 'ProcedureName';
	DECLARE @OverwriteProcedureFieldName varchar(MAX) = 'OverwriteProcedure';
	DECLARE @ProcedureExistsWarningFieldName varchar(MAX) = 'ProcedureExistsWarning';
	DECLARE @MainTableFieldName varchar(MAX) = 'MainTable';
	DECLARE @JoinFieldName varchar(MAX) = 'Joins';
	DECLARE @SelectColumnsFieldName varchar(MAX) = 'SelectColumns';
	DECLARE @WhereColumnsFieldName varchar(MAX) = 'WhereColumns';

	IF @Action = 'GetParameters'
	BEGIN
		SELECT * INTO #SoftadminGuideMultiControl FROM SoftadminGuide.Parameter_MultiControl_Definition();

		EXEC SoftadminGuide.Parameter_Heading_Create 
			@ParameterName = 'TableHeading',
			@Label = 'Table';

		EXEC SoftadminGuide.Parameter_Table_Create
			@ParameterName = @MainTableFieldName,
			@Label = 'Table';

		EXEC SoftadminGuide.Parameter_TableJoins_Create
			@ParameterName = @JoinFieldName,
			@Label = 'Joins',
			@ParentTableParameterName = @MainTableFieldName;

		EXEC SoftadminGuide.Parameter_Columns_Create
			@ParameterName = @SelectColumnsFieldName,
			@Label = 'Select columns',
			@TableParameterName = @MainTableFieldName,
			@JoinsParameterName = @JoinFieldName;

		EXEC SoftadminGuide.Parameter_ColumnsWithOperator_Create
			@ParameterName = @WhereColumnsFieldName,
			@Label = 'Where columns',
			@TableParameterName = @MainTableFieldName,
			@JoinsParameterName = @JoinFieldName;

		EXEC SoftadminGuide.Parameter_Heading_Create
			@ParameterName = 'ProcedureHeading',
			@Label = 'Procedure';

		EXEC SoftadminGuide.Parameter_SqlModuleName_Create
			@ParameterName = @ProcedureNameFieldName,
			@Label = 'Procedure name',
			@WarningParameterName = @ProcedureExistsWarningFieldName,
			@OverwriteProcedureParameterName = @OverwriteProcedureFieldName;

		UPDATE #SoftadminGuideMultiControl SET
			DefaultValueSql = SoftadminGuide.Parameter_SqlModuleName_FromTable_DefaultValueSql(@MainTableFieldName, '_Search')
		WHERE
			FieldName = @ProcedureNameFieldName;

		SELECT * FROM #SoftadminGuideMultiControl;

		RETURN;
	END;

	IF @Action = 'CreateObjects'
	BEGIN
		DECLARE @ReturnCode int = 0;
		DECLARE @ProcedureName nvarchar(600) = (SELECT X.[Value] FROM #MultiControlParameters X WHERE X.Id = @ProcedureNameFieldName);
		DECLARE @OverwriteProcedure bit = (SELECT X.[Value] FROM #MultiControlParameters X WHERE X.Id = @OverwriteProcedureFieldName);
		DECLARE @MainTableTableName nvarchar(600) = (SELECT SoftadminGuide.ParseParameter_Table_QuotedFullTableName(X.[Value]) FROM #MultiControlParameters X WHERE X.Id = @MainTableFieldName);
		DECLARE @MainTableObjectId int = (SELECT SoftadminGuide.ParseParameter_Table_ObjectId(X.[Value]) FROM #MultiControlParameters X WHERE X.Id = @MainTableFieldName);
		DECLARE @SelectColumns nvarchar(max) = (SELECT X.[Value] FROM #MultiControlParameters X WHERE X.Id = @SelectColumnsFieldName);
		DECLARE @WhereColumns nvarchar(max) = (SELECT X.[Value] FROM #MultiControlParameters X WHERE X.Id = @WhereColumnsFieldName);
		DECLARE @Join nvarchar(max) = (SELECT X.[Value] FROM #MultiControlParameters X WHERE X.Id = @JoinFieldName);

		BEGIN TRANSACTION;

		EXEC @ReturnCode = dbo.Grid_Example_Guide_Internal_CreateObjects
			@ProcedureName = @ProcedureName,
			@OverwriteProcedure = @OverwriteProcedure,
			@MainTableTableName = @MainTableTableName,
			@MainTableObjectId = @MainTableObjectId,
			@SelectColumns = @SelectColumns,
			@WhereColumns = @WhereColumns,
			@Join = @Join;

		COMMIT;

		RETURN @ReturnCode;
	END;
END;

Internal procedure

Responsible for procedure creation. Should be able to be called from any main procedure.

CREATE OR ALTER PROCEDURE dbo.Grid_Example_Guide_Internal_CreateObjects
	@ProcedureName varchar(600),
	@OverwriteProcedure bit,
	@MainTableTableName nvarchar(600),
	@MainTableObjectId int,
	@SelectColumns nvarchar(MAX),
	@WhereColumns nvarchar(MAX),
	@Join nvarchar(MAX)
AS
BEGIN
	DECLARE @Definition nvarchar(MAX) = 
'CREATE OR ALTER PROCEDURE {ProcedureName}
	{ParameterList}
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;

	SELECT
		{SelectColumnsString}
	FROM
		{MainTableName} {MainTableAlias}
		{Joins}
	{WhereColumnsString};
END;';

	DECLARE @NewLine varchar(2) = CHAR(13) + CHAR(10);

	DECLARE @ObjectIdProcedure int = OBJECT_ID(@ProcedureName);
	IF @ObjectIdProcedure IS NOT NULL AND @OverwriteProcedure = 0
	BEGIN
		SELECT
			CONCAT(N'Can not create the object "', @ProcedureName, '" since an object with that name already exists.') AS ADMIN_Message;
		RETURN SoftadminGuide.ReturnCode_NoLogging();
	END;

	SELECT
		*
	INTO
		#Joins
	FROM
		SoftadminGuide.ParseParameter_TableJoins(@Join);

	SELECT
		*
	INTO
		#SelectColumns
	FROM
		SoftadminGuide.ParseParameter_Columns(@SelectColumns);

	SELECT
		*
	INTO
		#WhereColumns
	FROM
		SoftadminGuide.ParseParameter_Columns(@WhereColumns);

	SELECT
		TA.Id,
		TA.Alias
	INTO
		#SoftadminGuideAlias
	FROM
		SoftadminGuide.Table_Aliases(@MainTableObjectId, @Join, NULL) TA

	DECLARE @MainTableAlias varchar(300) = (SELECT Alias FROM #SoftadminGuideAlias WHERE Id = CONVERT(varchar(30), @MainTableObjectId));

	DECLARE
		@SeparatorAnd varchar(20) = ' AND' + @NewLine,
		@SeparatorComma varchar(20) = ',' + @NewLine;
	
	SELECT
		W.ColumnName,
		W.DataType,
		A.Alias,
		W.ColumnName + IIF(C.SameNameCount > 1, '_' + A.Alias, '') UniqueColumnName,
		ROW_NUMBER() OVER(ORDER BY J.SortOrder, W.SortOrder) AS SortOrder
	INTO
		#WhereColumnName
	FROM
		#WhereColumns W
		JOIN #SoftadminGuideAlias A ON
			A.Id = W.Id
		LEFT JOIN #Joins J ON
			J.Id = A.Id
		CROSS APPLY (
			SELECT
				COUNT(*) AS SameNameCount
			FROM
				#WhereColumns WI
			WHERE
				WI.ColumnName = W.ColumnName
		) C
	
	DECLARE @SelectColumnsString nvarchar(max) =
		(
			SELECT
				SoftadminUtil.String_Join(A.Alias + '.' + S.ColumnName, @SeparatorComma + '		', S.SortOrder)
			FROM
				#SelectColumns S 
				JOIN #SoftadminGuideAlias A ON
					A.Id = S.Id
				LEFT JOIN #Joins J ON
					J.Id = A.Id
		);

	DECLARE @WhereColumnsString nvarchar(max) = 
		'WHERE' +  @NewLine +
		NULLIF(
			
				(
					SELECT
						SoftadminUtil.String_Join(
							'		('  + @NewLine + 
							'			' + W.Alias + '.' + W.ColumnName + ' ' + O.Operator + ' @' + W.UniqueColumnName + IIF(O.Operator = 'LIKE', ' + ''%''', '') + @NewLine + 
							'			OR' + @NewLine + 
							'			@' + W.UniqueColumnName + ' IS NULL' + @NewLine + 
							'		)',
							@SeparatorAnd,
							W.SortOrder
						)
					FROM
						#WhereColumnName W
						CROSS APPLY SaGuide.DataType_Operator(W.DataType) O
				),
		'');

	DECLARE @ParameterList nvarchar(max) =
		(
			SELECT
				SoftadminUtil.String_Join('@' + W.UniqueColumnName + ' ' + W.Datatype + ' = NULL', @SeparatorComma + '	', W.SortOrder)
			FROM
				#WhereColumnName W
		);

	DECLARE @Joins nvarchar(max) = 
		(
			SELECT
				SoftadminUtil.String_Join(
					'LEFT JOIN ' + J.QuotedFullTableName + ' ' + A.Alias + ' ON ' + AP.Alias + '.' + J.ParentColumnName + ' = ' + A.Alias + '.' + J.ReferencedColumnName,
					@NewLine  +'		',
					J.SortOrder
				)
			FROM
				#Joins J
				JOIN #SoftadminGuideAlias A ON J.Id = A.Id
				JOIN #SoftadminGuideAlias AP ON J.ParentId = AP.Id
		);

	CREATE TABLE #Replacement
	(
		SortOrder int IDENTITY NOT NULL,
		Placeholder varchar(300) NOT NULL,
		Replacement nvarchar(max) NULL,
		NullBehavior varchar(300) NULL
	);

	INSERT #Replacement (Placeholder, Replacement, NullBehavior)
	VALUES
	('{ProcedureName}', @ProcedureName, NULL),
	('{SelectColumnsString}', @SelectColumnsString, NULL),
	('{MainTableName}', @MainTableTableName, NULL),
	('{MainTableAlias}', @MainTableAlias, NULL),
	('{Joins}', @Joins, 'DeleteLine'),
	('{WhereColumnsString}', @WhereColumnsString, 'DeleteLineAndInsertPrevSemicolon'),
	('{ParameterList}', @ParameterList, 'DeleteLine')

	EXEC SoftadminGuide.TextTemplate_ReplacePlaceholders
		@Text = @Definition,
		@TextOut = @Definition OUTPUT;

	IF @OverwriteProcedure = 0
	BEGIN
		EXEC SoftadminGuide.SqlModule_Create
			@FullObjectName = @ProcedureName,
			@Definition = @Definition,
			@IsMainObject = 1;
	END;
	ELSE
	BEGIN
		EXEC SoftadminGuide.SqlModule_CreateOrAlter
			@FullObjectName = @ProcedureName,
			@Definition = @Definition,
			@IsMainObject = 1;
	END;
END;