Guide development

SQL

Examples

SQL guide - 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),
	@UserId int
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;

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

	IF @Action = 'GetFields'
	BEGIN
		/*
			Here you create the guide interface.
			The guide interface is one big multicontrol. Use #SoftadminGuideMultiControl to add input fields.
		*/

		SELECT * INTO #SoftadminGuideMultiControl FROM SoftadminGuide.Field_MultiControl_Definition();

		EXEC SoftadminGuide.FieldGeneric_Heading
			@FieldName = 'TableHeading',
			@Label = 'Table';

		-- Request name of an existing table.
		EXEC SoftadminGuide.FieldStandard_SqlObjectName
			@FieldName = @TableFieldName,
			@Label = 'Table',
			@AllowTables = 1,
			@AllowNonExisting = 0,
			@IsMandatory = 1;

		EXEC SoftadminGuide.FieldStandard_TableJoins
			@FieldName = @JoinFieldName,
			@Label = 'Joins',
			@ParentTableFieldName = @TableFieldName;

		EXEC SoftadminGuide.FieldStandard_Columns
			@FieldName = @SelectColumnsFieldName,
			@Label = 'Select columns',
			@TableFieldName = @TableFieldName,
			@JoinsFieldName = @JoinFieldName;

		EXEC SoftadminGuide.FieldStandard_ColumnsWithOperator
			@FieldName = @WhereColumnsFieldName,
			@Label = 'Where columns',
			@TableFieldName = @TableFieldName,
			@JoinsFieldName = @JoinFieldName;

		EXEC SoftadminGuide.FieldGeneric_Heading
			@FieldName = 'ProcedureHeading',
			@Label = 'Procedure';

		-- Request a procedure name, existing of not.
		EXEC SoftadminGuide.FieldStandard_SqlObjectName
			@FieldName = @ProcedureNameFieldName,
			@Label = 'Procedure name',
			@IsMandatory = 1,
			@AllowStoredProcedures = 1,
			@AllowNonExisting = 1;

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

		-- Generate warning and "overwrite confirmation checkbox" if the procedure name entered already exists.
		EXEC SoftadminGuide.FieldStandard_ExistingSqlObjectWarning
			@ObjectFieldName = @ProcedureNameFieldName,
			@ConfirmOverwriteCheckBoxFieldName = @OverwriteProcedureFieldName,
			@AllowStoredProcedures = 1;

		SELECT * FROM #SoftadminGuideMultiControl ORDER BY SortOrder;

		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 @TableId int = (SELECT X.[Value] FROM #MultiControlParameters X WHERE X.Id = @TableFieldName);
		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,
			@TableId = @TableId,
			@SelectColumns = @SelectColumns,
			@WhereColumns = @WhereColumns,
			@Join = @Join;

		COMMIT;

		RETURN @ReturnCode;
	END;
END;

SQL guide - Internal procedure

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

ALTER PROCEDURE dbo.Grid_Example_Guide_Internal_CreateObjects
	@ProcedureName varchar(600),
	@OverwriteProcedure bit,
	@TableName nvarchar(600),
	@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
		{TableName} {TableAlias}
		{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_ErrorMessage;
		RETURN SoftadminGuide.ReturnCode_Abort();
	END;

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

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

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

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

	DECLARE
		@TableAlias varchar(300) = (SELECT Alias FROM #SoftadminGuideAlias WHERE Id = CONVERT(varchar(900), OBJECT_ID(@TableName)));

	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.TableName + ' ' + 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),
	('{TableName}', @TableName, NULL),
	('{TableAlias}', @TableAlias, 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.SqlObject_Create
			@FullObjectName = @ProcedureName,
			@Definition = @Definition;
	END;
	ELSE
	BEGIN
		EXEC SoftadminGuide.SqlObject_CreateOrAlter
			@FullObjectName = @ProcedureName,
			@Definition = @Definition;
	END;
END;