SoftadminGuide.TextTemplate_ReplacePlaceholders

Show allHide all
Type: Stored procedure

Replaces placeholders in a text template with values. For example, a text template might look like

CREATE PROCEDURE {ProcedureName}
  @id {IdDataType}
AS
BEGIN
  SELECT
    {TableAlias}.{IdColumn},
    {ExtraColumns}
  FROM
    {TableName} {TableAlias}
  WHERE
    {TableAlias}.{IdColumn} = @Id
END

Expects the following temp table

CREATE TABLE #Replacement
(
	SortOrder int identity not null,
	Placeholder varchar(300) not null,
	Replacement nvarchar(max) null,
	NullBehavior varchar(300) null
);

SortOrder

The order in which the placeholders will be replaced in the template.

Placeholder

The text to replace, for example {IdColumn} or {TableName}. A word made up of only the characters a-z wrapped in {}.

Replacement

The text to replace the placeholder with. The replacement text is allowed to contain other placeholders but these must then have a higher sort order so that they are replaced after it.

NullBehavior

Special behavior when Replacement is null.

NULL

If replacement is never expected to be NULL then you should not specify NullBehavior

DeleteLine

Deletes the whole line the placeholder is on. For example

SELECT
  Id,
  Label,
  {ParentColumn} AS Parent,
  1 AS Selectable
FROM ...

becomes

SELECT
  Id,
  Label,
  1 AS Selectable
FROM ...
DeleteLineAndDeletePrevLine

Deletes the whole line and the previous line.

FROM ...
WHERE
  {WhereExpression}
ORDER BY
  ...

becomes

FROM ...
ORDER BY
   ...
DeleteLineAndDeleteNextLine

Deletes the whole line and the next line.

IF @Action = ''
BEGIN
  {OptionalStatement}

  RETURN;
END;
  ...

becomes

IF @Action = ''
BEGIN
  RETURN;
END;
DeleteLineAndDeletePrevComma

Deletes the whole line and if the previous line ends with a comma then that comma is deleted.

SELECT
  Id,
  Label,
  {ParentColumn} AS Parent
FROM ...

becomes

SELECT
  Id,
  Label
FROM ...
DeleteLineAndReplacePrevCommaWithSemicolon

Deletes the whole line and if the previous line ends with a comma that comma is replaced by a semicolon.

SELECT
  Id,
  Label,
  {ParentColumn} AS Parent;

becomes

SELECT
  Id,
  Label;
DeleteLineAndInsertPrevSemicolon

Deletes the whole line and if the previous line ends with a comma then that comma replaced by a semicolon.

FROM
  {Table} {TableAlias}
{WhereExpression};

becomes

FROM
  {Table} {TableAlias};

Parameters

@Text mandatory nvarchar(max)
The text to replace placeholders in.
@TextOut mandatory nvarchar(max)
Output parameter with the text with all placeholders replaced.