### tSQLt Installation and Setup Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts for the initial installation and setup of the tSQLt framework. These include creating assemblies, setting up CLR integration, and preparing the server environment for testing.

```sql
CREATE ASSEMBLY tSQLt
FROM 0x48656c6c6f0a
WITH PERMISSION_SET = SAFE;
```

```sql
EXEC tSQLt.InstallAssemblyKey;
```

```sql
EXEC tSQLt.PrepareServer;
```

--------------------------------

### Test Class Setup and Cleanup

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

This snippet illustrates setting up a test class with common Setup and Cleanup procedures. The Setup procedure runs before each test in the class, typically for faking tables and inserting common test data. The Cleanup procedure runs after each test, useful for releasing resources or performing specific post-test actions. Test procedures within the class automatically leverage these setup and teardown routines.

```sql
-- Create test class
EXEC tSQLt.NewTestClass 'OrderProcessingTests';
GO

-- Setup procedure runs before EACH test in the class
CREATE PROCEDURE OrderProcessingTests.Setup
AS
BEGIN
  -- Common setup for all tests in this class
  EXEC tSQLt.FakeTable 'dbo.Orders';
  EXEC tSQLt.FakeTable 'dbo.OrderItems';
  EXEC tSQLt.FakeTable 'dbo.Customers';

  -- Insert common test data
  INSERT INTO dbo.Customers (CustomerId, Name, Email)
  VALUES (1, 'Test Customer', 'test@example.com');
END;
GO

-- Cleanup procedure runs after EACH test in the class
CREATE PROCEDURE OrderProcessingTests.Cleanup
AS
BEGIN
  -- Additional cleanup if needed (transaction rollback handles most cleanup)
  -- Useful for cleaning up server-level objects or external resources
  PRINT 'Test completed';
END;
GO

-- Test procedures automatically use Setup and Cleanup
CREATE PROCEDURE OrderProcessingTests.[test new order creates order record]
AS
BEGIN
  -- Setup has already executed - tables are faked and customer exists

  EXEC dbo.CreateOrder @CustomerId = 1, @TotalAmount = 100.00;

  DECLARE @OrderCount INT;
  SELECT @OrderCount = COUNT(*) FROM dbo.Orders;

  EXEC tSQLt.AssertEquals 1, @OrderCount;

  -- Cleanup will execute after this test completes
END;
GO
```

--------------------------------

### Get tSQLt Installation Information (SQL Function)

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

This SQL function, tSQLt.Private_InstallationInfo.sfn.sql, retrieves information about the tSQLt installation. It provides details such as the installed version, installation date, and potentially other configuration settings. This function is internal and likely used by other tSQLt components for version checking or compatibility.

```sql
CREATE FUNCTION tSQLt.Private_InstallationInfo() RETURNS TABLE
AS
RETURN
(
    SELECT
        CONVERT(INT, tSQLt.Internal_GetValue('InstallationMajorVersion')) AS InstallationMajorVersion,
        CONVERT(INT, tSQLt.Internal_GetValue('InstallationMinorVersion')) AS InstallationMinorVersion
);
GO
```

--------------------------------

### tSQLt PrepareServer.sql Installation Script

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This outlines the process of using the PrepareServer.sql script provided with tSQLt. This script enables CLR and installs a server certificate, simplifying tSQLt installation by eliminating the need to disable strict CLR security or modify database settings. It requires SA permissions and is run once per server.

```tsql
-- Run this script once per server before installing tSQLt
-- (Requires SA permissions)
-- EXEC PrepareServer.sql;
```

--------------------------------

### tSQLt New Prepare Server Process

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This describes a new 'Prepare Server' process introduced in tSQLt, executed via PrepareServer.sql. This script automates enabling CLR and installing a server certificate, simplifying installation on SQL Server 2017 and 2019 without disabling security settings.

```tsql
-- Execute this script once per server before installing tSQLt (Requires SA permissions)
-- EXEC PrepareServer.sql;

-- After installation, if using tSQLt.NewConnection, run:
-- EXEC tSQLt.EnableExternalAccess;
```

--------------------------------

### Create and Start Docker Container

Source: https://github.com/tsqlt-org/tsqlt/blob/main/CI/AKS/docker/README.md

Creates a Docker container from an image, mapping a host port to the container's SQL Server port (1433). It then starts the specified container.

```shell
docker container create -p HOSTPORT:1433 IMAGENAME
```

```shell
docker container start CONTAINERNAME
```

--------------------------------

### tSQLt Installation with DACPACs

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This describes the alternative installation method for tSQLt using DACPACs, in addition to the traditional tSQLt.class.sql script. It notes that DACPACs are SQL Server version specific, unlike the script-based installation.

```powershell
# Example using DacFx PowerShell cmdlets (requires installation)
# Install-DacPackage -SourcePath "path\to\tSQLt.dacpac" -DatabaseName "YourDatabase"
```

--------------------------------

### Install External Access Key for tSQLt

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

Installs necessary objects in the 'master' database to enable tSQLt to execute with EXTERNAL_ACCESS permissions. This is an alternative to setting the database to TRUSTWORTHY.

```sql
tSQLt.InstallExternalAccessKey
```

--------------------------------

### Get tSQLt Information

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

Retrieves information about the installed tSQLt CLR assembly, including its version, the SQL Server version, and the public key token of the signing key. This helps in managing and verifying tSQLt installations.

```sql
SELECT * FROM tSQLt.Info() AS I;
```

--------------------------------

### Install tSQLt External Access Key (SQL)

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

This script installs an external access key for the tSQLt framework. It is essential for enabling certain advanced features or integrations that require external authentication or authorization within the tSQLt environment. No specific input parameters are detailed, and it operates directly on the database schema.

```sql
CREATE SCHEMA tSQLt;
GO

CREATE ASSEMBLY tSQLt
FROM 0x4D5A900002000000048000000000000000000000040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001;

```

--------------------------------

### tSQLt Framework Footer (SQL)

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

The tSQLt._Footer.sql script likely contains closing statements or cleanup routines required for the tSQLt framework installation or execution. It might include end-of-script markers, finalization steps, or essential configuration settings that are applied after the main installation or testing processes are complete. This script is often a part of a larger installation package.

```sql
-- tSQLt Framework Footer
-- Contains finalization scripts or closing statements.

-- Example: Setting database compatibility level if needed
-- ALTER DATABASE SCOPED CONFIGURATION SET COMPATIBILITY_LEVEL = 150;

-- Example: Finalizing schema creation or object grants
-- GRANT EXECUTE ON SCHEMA::tSQLt TO PUBLIC;

GO
```

--------------------------------

### Execute SQL Command with sqlcmd

Source: https://github.com/tsqlt-org/tsqlt/blob/main/CI/AKS/docker/README.md

Executes a SQL command using 'sqlcmd' with username, password, and server details. This example retrieves the SQL Server version.

```shell
sqlcmd -U sa -P "W3lc0mE002" -S ".",41433 -Q "SELECT @@VERSION"
```

--------------------------------

### tSQLt EnableExternalAccess for NewConnection

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This note reminds users that if they are utilizing the tSQLt.NewConnection feature, they must execute tSQLt.EnableExternalAccess after each installation of tSQLt. This ensures that external access, required by NewConnection, is properly configured.

```tsql
-- After installing tSQLt, if using tSQLt.NewConnection:
EXEC tSQLt.EnableExternalAccess;
```

--------------------------------

### tSQLt AKS Prototyping for SQL Server Hosting

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This describes a prototype setup using Azure Kubernetes Service (AKS) to host various versions of SQL Server (2014, 2016, 2017, and 2019). This explores containerization and orchestration possibilities for running SQL Server instances used with tSQLt.

```yaml
# Kubernetes deployment manifest example for SQL Server on AKS
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-server-2019
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mssql-server
  template:
    metadata:
      labels:
        app: mssql-server
    spec:
      containers:
      - name: mssql-server
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          value: "YourStrong@Password123"
        # ... volume mounts for persistent storage
```

--------------------------------

### tSQLt FakeFunction with Non-Functions

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This snippet shows how FakeFunction in tSQLt can now accept non-function data sources, such as example tables or SELECT statements. This improves the flexibility of FakeFunction for mocking various data scenarios.

```tsql
EXEC tSQLt.FakeFunction 'YourSchema', 'YourFunction', 'SELECT * FROM YourTable';
```

```tsql
EXEC tSQLt.FakeFunction 'YourSchema', 'YourFunction', 'SELECT ''Value1'' AS ColA, 100 AS ColB';
```

--------------------------------

### tSQLt FakeFunction with VALUES Clause

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This example illustrates the capability of tSQLt.FakeFunction to accept a 'VALUES' clause as a data source, allowing it to mimic a table-valued function or a SELECT statement for testing purposes. This enhances flexibility when creating fake data sources.

```tsql
EXEC tSQLt.FakeFunction 'YourSchema', 'YourFunction', 'SELECT 1 AS Col1, ''Test'' AS Col2 FROM (VALUES (1, ''A''), (2, ''B'')) AS T(Val1, Val2)';
```

--------------------------------

### Spy Procedure with Alternative Command in T-SQL

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Demonstrates an advanced use of tSQLt.SpyProcedure where an alternative command is executed instead of the original procedure. This is useful for mocking dependencies and controlling the side effects of procedure calls during testing. The example shows how to insert data into a temporary table when the spied procedure is invoked.

```sql
CREATE PROCEDURE MyTests.[test order processing with mocked email service]
AS
BEGIN
  -- Spy procedure but execute alternative logic instead
  EXEC tSQLt.SpyProcedure
    @ProcedureName = 'dbo.SendOrderConfirmationEmail',
    @CommandToExecute = 'INSERT INTO #EmailsSent (Recipient) VALUES (''mock@test.com'')';

  CREATE TABLE #EmailsSent (Recipient NVARCHAR(100));

  -- When SendOrderConfirmationEmail is called, it will execute the CommandToExecute
  EXEC dbo.ProcessOrder @OrderId = 1;

  -- Verify mock command was executed
  DECLARE @EmailCount INT;
  SELECT @EmailCount = COUNT(*) FROM #EmailsSent;
  EXEC tSQLt.AssertEquals 1, @EmailCount;
END;
GO
```

--------------------------------

### Spy Procedure and Verify Calls in T-SQL

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Demonstrates using tSQLt.SpyProcedure to monitor calls to another stored procedure. It replaces the target procedure with a spy, logging its execution details. The example shows how to query the generated log table to verify if the procedure was called and with what parameters. This is crucial for testing interactions between procedures.

```sql
CREATE PROCEDURE AcceleratorTests.[test email is sent if we detected a higgs-boson]
AS
BEGIN
  -- Replace procedure with spy to capture calls
  EXEC tSQLt.SpyProcedure 'Accelerator.SendHiggsBosonDiscoveryEmail';

  -- Execute code that should call the spied procedure
  EXEC Accelerator.AlertParticleDiscovered 'Higgs Boson';

  -- Spy automatically creates log table: ProcedureName_SpyProcedureLog
  -- Query the log to verify procedure was called with correct parameters
  SELECT EmailAddress
    INTO #Actual
    FROM Accelerator.SendHiggsBosonDiscoveryEmail_SpyProcedureLog;

  SELECT TOP(0) * INTO #Expected FROM #Actual;
  INSERT INTO #Expected (EmailAddress)
  VALUES ('particle-discovery@new-era-particles.tsqlt.org');

  -- Verify procedure was called with expected email address
  EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';
END;
GO

CREATE PROCEDURE MyTests.[test audit log procedure called three times]
AS
BEGIN
  EXEC tSQLt.SpyProcedure 'dbo.WriteAuditLog';

  -- Execute business logic
  EXEC dbo.ProcessCustomerOrders @CustomerId = 1;

  -- Verify WriteAuditLog was called exactly 3 times
  DECLARE @CallCount INT;
  SELECT @CallCount = COUNT(*) FROM dbo.WriteAuditLog_SpyProcedureLog;

  EXEC tSQLt.AssertEquals
    @Expected = 3,
    @Actual = @CallCount,
    @Message = 'WriteAuditLog should be called 3 times';

  -- Original procedure automatically restored after test
END;
GO
```

--------------------------------

### Execute SQL Command in Docker Container

Source: https://github.com/tsqlt-org/tsqlt/blob/main/CI/AKS/docker/README.md

Executes a SQL command within a running Docker container using 'sqlcmd'. This example retrieves the SQL Server version.

```shell
docker container exec 93d676bf0088 sqlcmd -E -S ".",1433 -Q "SELECT @@VERSION"
```

--------------------------------

### Fake Table with Options in T-SQL

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Illustrates using tSQLt.FakeTable to isolate tables for testing by removing constraints and data. This example shows how to control identity column behavior, computed columns, and default constraints during faking. It's essential for creating controlled test environments.

```sql
CREATE PROCEDURE MyTests.[test order processing with isolated data]
AS
BEGIN
  -- Fake the Orders table - removes constraints and empties data
  EXEC tSQLt.FakeTable
    @TableName = 'dbo.Orders',
    @Identity = 1,          -- Preserve identity column behavior
    @ComputedColumns = 0,   -- Remove computed columns
    @Defaults = 0;          -- Remove default constraints

  -- Now you can insert any data without constraint violations
  INSERT INTO dbo.Orders (OrderId, CustomerId, Total)
  VALUES (1, 999, 100.00);  -- CustomerId 999 doesn't need to exist

  -- Fake related table
  EXEC tSQLt.FakeTable 'dbo.OrderItems';
  INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity)
  VALUES (1, 888, 5);  -- ProductId 888 doesn't need to exist

  -- Test your code with complete control over test data
  DECLARE @Total MONEY;
  SELECT @Total = dbo.CalculateOrderTotal(1);

  EXEC tSQLt.AssertEquals 100.00, @Total;

  -- Transaction automatically rolls back after test
  -- Original tables and constraints automatically restored
END;
GO
```

--------------------------------

### tSQLt MaxSqlMajorVersion Annotation Usage

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This example shows the usage of the --[@tSQLt:MaxSqlMajorVersion](@MaxVersion) annotation. It's used to skip tests if the SQL Server major version is greater than the specified @MaxVersion, ensuring compatibility.

```tsql
-- Test case annotation to skip if SQL Server major version > 2017
--[@tSQLt:MaxSqlMajorVersion](2017)
CREATE PROCEDURE MySchema.TestSomethingOnlyOnOlderVersions
AS
BEGIN
  -- Test logic here
END;
```

--------------------------------

### Remove External Access Key for tSQLt

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

Removes the objects previously installed by tSQLt.InstallExternalAccessKey from the 'master' database. This is used to revoke the permissions granted for EXTERNAL_ACCESS.

```sql
tSQLt.RemoveExternalAccessKey
```

--------------------------------

### tSQLt MaxSqlMajorVersion Annotation

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This example demonstrates the @tSQLt:MaxSqlMajorVersion annotation, which allows tests to be skipped if the SQL Server major version exceeds the specified @MaxVersion. This is useful for ensuring tests run only on compatible SQL Server versions.

```tsql
-- In a test method:
--[@tSQLt:MaxSqlMajorVersion](2017)
CREATE PROCEDURE YourSchema.[Test_Feature_Only_On_Older_Versions]
AS
BEGIN
  -- Test logic here...
END;
```

--------------------------------

### Fake Function by Mocking Another Function

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

This example shows how to use tSQLt.FakeFunction to redirect calls from one function (e.g., dbo.GetTaxRate) to another, specific test function (e.g., dbo.GetTestTaxRate). This allows for mocking complex calculations or external service calls with controlled test implementations. The test then asserts the expected outcome based on the mocked function's return value.

```sql
CREATE PROCEDURE MyTests.[test tax calculation uses mocked tax rate function]
AS
BEGIN
  -- Redirect function calls to alternative implementation
  EXEC tSQLt.FakeFunction
    @FunctionName = 'dbo.GetTaxRate',
    @FakeFunctionName = 'dbo.GetTestTaxRate';  -- Call this function instead

  -- Now all calls to GetTaxRate() will actually call GetTestTaxRate()
  DECLARE @Tax MONEY;
  SELECT @Tax = dbo.CalculateTax(@Amount = 100.00, @Region = 'CA');

  EXEC tSQLt.AssertEquals 10.00, @Tax;  -- Assuming test function returns 10%
END;
GO
```

--------------------------------

### Manually Fail Test with Custom Message

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

This example demonstrates how to manually fail a tSQLt test case using the tSQLt.Fail procedure. It's used when a specific condition within the test logic indicates a failure that isn't directly covered by assertion methods. The tSQLt.Fail procedure allows for multiple string parameters, which are concatenated to form the failure message, providing detailed context.

```sql
CREATE PROCEDURE MyTests.[test complex business rule validation]
AS
BEGIN
  DECLARE @ValidationErrors TABLE (ErrorMessage NVARCHAR(MAX));

  -- Execute complex validation
  INSERT INTO @ValidationErrors
  EXEC dbo.ValidateComplexBusinessRule @Data = 'test data';

  -- Custom validation logic
  IF EXISTS (SELECT 1 FROM @ValidationErrors WHERE ErrorMessage LIKE '%critical%')
  BEGIN
    DECLARE @ErrorDetails NVARCHAR(MAX);
    SELECT @ErrorDetails = STRING_AGG(ErrorMessage, ', ')
    FROM @ValidationErrors;

    -- Manually fail test with detailed message
    EXEC tSQLt.Fail
      'Critical validation errors found: ',
      @ErrorDetails,
      ' - Business rule validation failed';
    -- Can pass up to 10 message parameters that get concatenated
  END;

  -- If we reach here, test passes
END;
GO
```

--------------------------------

### Undo Single Test Double (SQL Procedure)

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

The tSQLt.Private_UndoSingleTestDouble.ssp.sql script defines a stored procedure to undo a single test double. Test doubles are used in unit testing to isolate the system under test. This procedure allows for granular removal of specific test doubles, aiding in test setup and cleanup. It likely takes the name of the double to be removed as a parameter.

```sql
CREATE PROCEDURE tSQLt.Private_UndoSingleTestDouble
    @SchemaName NVARCHAR(MAX),
    @TableName NVARCHAR(MAX)
AS
BEGIN
    -- Implementation to undo a single test double
    -- (Actual code omitted for brevity, would involve dropping the doubled object and restoring the original)
END;
GO
```

--------------------------------

### Create and Run tSQLt Tests

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Demonstrates how to create a new test class using tSQLt.NewTestClass, define a test procedure, and then run tests using tSQLt.RunAll, tSQLt.Run, or tSQLt.RunWithXmlResults for CI/CD integration.

```sql
-- Create a new test schema to organize related tests
EXEC tSQLt.NewTestClass 'AcceleratorTests';
GO

-- Create a test procedure within the test class
CREATE PROCEDURE AcceleratorTests.[test ready for experimentation if 2 particles]
AS
BEGIN
  -- Arrange: Set up test data
  EXEC tSQLt.FakeTable 'Accelerator.Particle';
  INSERT INTO Accelerator.Particle (Id) VALUES (1);
  INSERT INTO Accelerator.Particle (Id) VALUES (2);

  DECLARE @Ready BIT;

  -- Act: Execute the code under test
  SELECT @Ready = Accelerator.IsExperimentReady();

  -- Assert: Verify the expected result
  EXEC tSQLt.AssertEquals 1, @Ready;
END;
GO

-- Run all tests in the database
EXEC tSQLt.RunAll;

-- Run all tests in a specific test class
EXEC tSQLt.Run 'AcceleratorTests';

-- Run a specific test case
EXEC tSQLt.Run 'AcceleratorTests.[test ready for experimentation if 2 particles]';

-- Run tests with XML output (JUnit format for CI/CD integration)
EXEC tSQLt.RunWithXmlResults 'AcceleratorTests';

-- Example output:
-- +------------------------------------------------------------------+
-- |Test Execution Summary                                            |
-- +------------------------------------------------------------------+
-- |TestCase                                          |Result |Duration|
-- |AcceleratorTests.test ready for...                |Success|00:00:00|
-- |AcceleratorTests.test we are not ready...         |Success|00:00:00|
-- +------------------------------------------------------------------+

```

--------------------------------

### Load Signing Key with sn Tool

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ChangeSigningKey_ReadMe.txt

This command loads a signing key (SigningKey.pfx) into a specified key container (VS_KEY_FFAB74CD53FE74BA) using the 'sn' tool. This is typically done from the Visual Studio Command Prompt. The user will be prompted for the key's password.

```shell
sn -i SigningKey.pfx VS_KEY_FFAB74CD53FE74BA
```

--------------------------------

### tSQLt GitHub Action for Build and Test

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This describes the creation of a GitHub Action for building and testing tSQLt on MSSQL 2017 and 2019 using Redgate Spawn. This automates the CI/CD process for tSQLt development.

```yaml
name: tSQLt Build and Test

on: [push, pull_request]

jobs:
  build_and_test:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v2
    - name: Setup tSQLt
      # This step would involve setting up SQL Server (e.g., mcr.microsoft.com/mssql/server:2019-latest)
      # and then deploying tSQLt via DACPAC or script.
      run: |
        echo "Setting up SQL Server and tSQLt..."
        # Placeholder for actual setup commands
    - name: Run tSQLt Tests
      # This step would execute tSQLt tests against the setup database.
      run: |
        echo "Running tSQLt tests..."
        # Placeholder for actual test execution commands
```

--------------------------------

### tSQLt Utility and Helper Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

A collection of utility scripts that support various functionalities within tSQLt, such as logging captured output, renaming objects, and formatting SQL server versions.

```sql
EXEC tSQLt.LogCapturedOutput 'MyLogTable';
```

```sql
EXEC tSQLt.Private_RenameObject 'Schema', 'OldName', 'NewName';
```

```sql
SELECT tSQLt.FriendlySQLServerVersion() AS Version;
```

```sql
EXEC tSQLt.RemoveObject 'MyObject';
```

```sql
EXEC tSQLt.RemoveObjectIfExists 'MyObject';
```

--------------------------------

### tSQLt Build Process Improvements (NAnt to Ant)

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This update mentions improvements to the tSQLt build process, specifically switching from NAnt to Ant. This indicates a transition in the build automation tools used for the project.

```xml
<!-- Example Ant build.xml snippet for tSQLt build tasks -->
<project name="tSQLtBuild" default="build">
  <target name="build" description="Builds the tSQLt framework">
    <!-- Ant tasks for compilation, testing, packaging, etc. -->
    <!-- Example: <mkdir dir="${build.dir}"/> -->
    <!-- Example: <java jar="${compiler.jar}" ... /> -->
    <echo message="tSQLt build process using Ant."/>
  </target>
</project>
```

--------------------------------

### Test Creation and Execution

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

APIs for creating new test classes and running various test scopes, including all tests, specific classes, or individual test cases. Supports XML output for CI/CD integration.

```APIDOC
## Create Test Class

### Description
Creates a new schema to organize related database tests.

### Method
`EXEC tSQLt.NewTestClass 'SchemaName';`

### Endpoint
N/A (Stored Procedure)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
EXEC tSQLt.NewTestClass 'AcceleratorTests';
GO
```

### Response
None (creates a schema)

## Run Tests

### Description
Executes database tests at different granularities (all, by class, by specific test). Supports XML output.

### Method
`EXEC tSQLt.RunAll;`
`EXEC tSQLt.Run 'SchemaName';`
`EXEC tSQLt.Run 'SchemaName.TestName';`
`EXEC tSQLt.RunWithXmlResults 'SchemaName';`

### Endpoint
N/A (Stored Procedure)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
-- Run all tests
EXEC tSQLt.RunAll;

-- Run tests in a specific class
EXEC tSQLt.Run 'AcceleratorTests';

-- Run a specific test case
EXEC tSQLt.Run 'AcceleratorTests.[test ready for experimentation if 2 particles]';

-- Run tests with XML output
EXEC tSQLt.RunWithXmlResults 'AcceleratorTests';
```

### Response
Returns test execution summary (plain text or XML).
```

--------------------------------

### Build Docker Image

Source: https://github.com/tsqlt-org/tsqlt/blob/main/CI/AKS/docker/README.md

Builds a Docker image using a Dockerfile. It can be executed from the directory containing the Dockerfile or from a parent directory by specifying the path to the Dockerfile.

```shell
docker build -f <DIRECTORY>/Dockerfile .
```

```shell
docker build -f Dockerfile ..
```

--------------------------------

### tSQLt Facade Dacpac Prototyping for Visual Studio

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This details the prototyping of Facade dacpacs for use within Visual Studio when developing tSQLt tests. While a full tSQLt dacpac was ultimately chosen, this work involved retaining the original Facade code and tests.

```xml
<!-- Example .sqlproj file structure for a dacpac project -->
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">
      Debug
    </Configuration>
    <Platform Condition=" '$(Platform)' == '' ">
      $(Platform)
    </Platform>
    <ProjectGuid>
      {YOUR-GUID-HERE}
    </ProjectGuid>
    <Name>
      tSQLt.Facade.Test
    </Name>
    <SchemaVersion>
      1.0
    </SchemaVersion>
    <OutputType>
      Package
    </OutputType>
    <AppDesignerFolder>
      Properties
    </AppDesignerFolder>
    <RootNamespace>
      tSQLt.Facade.Test
    </RootNamespace>
    <AssemblyName>
      tSQLt.Facade.Test
    </AssemblyName>
    <DeterministicClient
      False
    </DeterministicClient>
  </PropertyGroup>
  <PropertyGroup>
    <VisualStudioVersion Condition=" '$(VisualStudioVersion)' == '' ">
      11.0
    </VisualStudioVersion>
    <SSDT_EngineVersion>
      15.0
    </SSDT_EngineVersion>
  </PropertyGroup>
  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.targets" />
  <ItemGroup>
    <Folder Include="Properties" />
  </ItemGroup>
  <ItemGroup>
    <Compile Include="**\*.sql
```

--------------------------------

### tSQLt Fake and Stub Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts for creating fake objects (tables, functions) and stubs for procedures to isolate the system under test. These help in controlling dependencies during testing.

```sql
EXEC tSQLt.FakeTable 'MyTable';
```

```sql
EXEC tSQLt.FakeFunction 'MySchema.MyFunction', 'RETURN 1';
```

```sql
EXEC tSQLt.SpyProcedure 'MySchema.MyProcedure';
```

```sql
EXEC tSQLt.StubRecord 'MyTable', 'MyColumn', '123';
```

--------------------------------

### tSQLt Test Case Management Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts related to the creation, management, and execution of test cases within tSQLt. This includes creating new test classes, failing tests, and enabling/disabling external access.

```sql
EXEC tSQLt.NewTestClass 'MyTestClass';
```

```sql
EXEC tSQLt.Fail 'This is a failing test';
```

```sql
EXEC tSQLt.EnableExternalAccess;
```

```sql
EXEC tSQLt.RenameClass 'OldTestClassName', 'NewTestClassName';
```

--------------------------------

### tSQLt Reset and Cleanup Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts for resetting the test environment and cleaning up temporary objects created during test execution.

```sql
EXEC tSQLt.Reset;
```

```sql
EXEC tSQLt.Private_CleanUp;
```

--------------------------------

### Basic Value Assertions

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

API endpoints for performing basic assertions on scalar values, strings, and checking for inequality.

```APIDOC
## Assert Equals

### Description
Asserts that two scalar values are equal.

### Method
`EXEC tSQLt.AssertEquals @Expected, @Actual, @Message;`

### Endpoint
N/A (Stored Procedure)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
EXEC tSQLt.AssertEquals
  @Expected = 100,
  @Actual = @CalculatedValue,
  @Message = 'Calculation did not return the expected value';
```

### Response
None (test fails if assertion is false)

## Assert Equals String

### Description
Asserts that two string values are exactly equal.

### Method
`EXEC tSQLt.AssertEqualsString @Expected, @Actual, @Message;`

### Endpoint
N/A (Stored Procedure)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
EXEC tSQLt.AssertEqualsString
  'The status message is correct.',
  @ActualStatusMessage;
```

### Response
None (test fails if strings do not match exactly)

## Assert Not Equals

### Description
Asserts that two scalar values are not equal.

### Method
`EXEC tSQLt.AssertNotEquals @Expected, @Actual, @Message;`

### Endpoint
N/A (Stored Procedure)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
EXEC tSQLt.AssertNotEquals
  @Expected = @OrderId1,
  @Actual = @OrderId2,
  @Message = 'Order IDs should be unique';
```

### Response
None (test fails if assertion is false)
```

--------------------------------

### tSQLt Build Migration from Ant to PowerShell

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This notes the migration of parts of the tSQLt build process from Ant to PowerShell. This indicates a shift towards more modern scripting and automation tools for building and managing the tSQLt project.

```powershell
# Example PowerShell script for tSQLt build tasks
# This replaces ANT build scripts for certain tasks.

param(
    [string]$BuildConfiguration = "Release"
)

Write-Host "Starting tSQLt build process using PowerShell..."

# Invoke-Expression "sqlcmd -S . -d master -i ..\Install\tSQLt.class.sql"
# Invoke-Dacpac "..."

Write-Host "tSQLt build process completed."
```

--------------------------------

### tSQLt Exception Handling Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts for handling expected exceptions during test execution. This allows tests to verify that specific error conditions are met.

```sql
EXEC tSQLt.ExpectException 'MyCustomError';
```

```sql
EXEC tSQLt.ExpectNoException;
```

--------------------------------

### tSQLt Trigger Application Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts used to apply triggers within the tSQLt framework, often in conjunction with faking objects to capture or modify behavior.

```sql
EXEC tSQLt.ApplyTrigger 'MyTable', 'MyTrigger';
```

--------------------------------

### tSQLt FriendlySQLServerVersion Function

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This snippet shows how to use the tSQLt.FriendlySQLServerVersion function, which returns the SQL Server version as a user-friendly string (e.g., '2019', '2008R2'). This function simplifies identifying the SQL Server environment during testing.

```tsql
SELECT tSQLt.FriendlySQLServerVersion();
```

--------------------------------

### tSQLt Annotation Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts that implement test annotations for controlling test execution based on various criteria like skipping tests or version requirements.

```sql
EXEC tSQLt.SkipTestAnnotationHelper 'Reason';
```

```sql
EXEC tSQLt.NoTransaction;
```

--------------------------------

### tSQLt SpyProcedure Optional Parameter and Variable

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This snippet demonstrates the usage of the new optional @CallOriginal parameter in SpyProcedure and the availability of the @SpyProcedureOriginalObjectName variable within CommandToExecute. These enhancements provide more control and information when using SpyProcedure for mocking stored procedures.

```tsql
EXEC tSQLt.SpyProcedure 'YourProcedure', @spyProcedure_Param1 = 'some value', @CallOriginal = 1;
```

```tsql
-- Inside CommandToExecute:
DECLARE @ObjectName NVARCHAR(MAX) = @SpyProcedureOriginalObjectName;
```

--------------------------------

### tSQLt Azure DevOps Pipeline Refactoring

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This details the refactoring of the Azure DevOps Pipeline for tSQLt. The changes include using more powerful VMs and leveraging the Azure CLI (azcli) with a Bicep file to create SQL Virtual Machines, optimizing the build and deployment process.

```yaml
# Azure DevOps pipeline snippet (YAML)
# Example using Azure CLI and Bicep for VM creation

pool: 
  vmImage: 'ubuntu-latest' # Or a specific agent pool with required software

steps:
- task: AzureCLI@2
  inputs:
    azureSubscription: 'YourAzureServiceConnection'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      az bicep build --file vm.bicep
      az deployment group create --resource-group YourResourceGroup --template-file vm.bicep --parameters vmName=tSQLtVM sqlAdminLogin=testuser sqlAdminPassword=Pa$$w0rd
      # ... further steps to install SQL Server and run tSQLt tests
```

--------------------------------

### tSQLt Assertion Scripts

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Source/BuildOrder.txt

Scripts used for asserting conditions within tSQLt tests. These cover various assertion types like checking for object existence, comparing string values, and verifying table content and schema.

```sql
EXEC tSQLt.AssertObjectExists 'MyTable';
```

```sql
EXEC tSQLt.AssertEqualsString 'Expected', 'Actual';
```

```sql
EXEC tSQLt.AssertEqualsTable 'ExpectedTable', 'ActualTable';
```

```sql
EXEC tSQLt.AssertObjectDoesNotExist 'MyObsoleteTable';
```

```sql
EXEC tSQLt.AssertLike 'Pattern', 'String';
```

```sql
EXEC tSQLt.AssertNotEquals 'Value1', 'Value2';
```

```sql
EXEC tSQLt.AssertEmptyTable 'MyTable';
```

```sql
EXEC tSQLt.AssertEqualsTableSchema 'ExpectedSchemaTable', 'ActualSchemaTable';
```

--------------------------------

### tSQLt Private_GetFullTypeName Fractional Seconds Fix

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This bug fix resolves an issue in tSQLt.Private_GetFullTypeName where it did not correctly handle fractional seconds precision for date and time types. The fix, by saperry, ensures accurate type name reporting for these data types.

```tsql
-- The fix likely involves modifying the logic within tSQLt.Private_GetFullTypeName
-- to correctly parse and include the '(<precision>)' part for datetime2 and time types.
```

--------------------------------

### tSQLt CLR Signed with New Key

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

This release note announces that the tSQLt CLR assembly is now signed with a new public key and token. This is a security update and may require trust adjustments if applications rely on the previous key.

```text
Public key (hash algorithm: sha1):
0024000004800000940000000602000000240000525341310004000001000100b9af416ad8dfec
dec08a5652fa257f1242bf4ed60ef5a7b84a429604d62c919c5663a9c7710a7c5df9953b69ec89f
ce85d71e051140b273f4c9bf890a2bc19c48f22d7b1f1d739f90eebc5729555f7f8b63ed088bbb
083b336f7e38b92d44cfe1c842f09632b85114772ff2122bc638c78d497c4e88c2d656c166050d
6e1ef394

Public key token is e8fff6f136d7b53e
```

--------------------------------

### SQL: Assert string matches a LIKE pattern

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Asserts that an actual string value matches a SQL LIKE pattern. Useful for validating error messages or string outputs. The test passes if the @Actual string conforms to the @ExpectedPattern. Dependencies include the tSQLt framework.

```sql
CREATE PROCEDURE MyTests.[test error message contains user name]
AS
BEGIN
  DECLARE @ErrorMessage NVARCHAR(MAX);

  BEGIN TRY
    EXEC dbo.DeleteUser @UserId = 999;
  END TRY
  BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();
  END CATCH

  EXEC tSQLt.AssertLike
    @ExpectedPattern = '%User%not found%',
    @Actual = @ErrorMessage,
    @Message = 'Error message should indicate user not found';
  -- Passes if @Actual matches the LIKE pattern
END;
GO
```

--------------------------------

### tSQLt Assert Equals for Value Comparison

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

This snippet demonstrates the use of tSQLt.AssertEquals to verify if two scalar values are equal. It's commonly used to check the return value of functions or stored procedures against an expected outcome, with an optional message for failure.

```sql
CREATE PROCEDURE MyTests.[test calculation returns expected result]
AS
BEGIN
  DECLARE @Expected INT = 100;
  DECLARE @Actual INT;

  -- Execute code that should return 100
  SELECT @Actual = dbo.CalculateTotalPrice(@ItemId = 5, @Quantity = 10);

  -- Assert the values are equal
  EXEC tSQLt.AssertEquals
    @Expected = @Expected,
    @Actual = @Actual,
    @Message = 'Total price calculation failed';
  -- If assertion fails, test fails with message:
  -- "Total price calculation failed [Expected: 100] but was: [95]"
END;
GO

```

--------------------------------

### Enable Verbose Execution Mode in tSQLt

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

Configures tSQLt to output the test name at the beginning and end of each test's execution. This is useful for tracking progress in large test suites. To disable, set @Verbose to 0.

```sql
EXEC tSQLt.SetVerbose @Verbose = 1;
```

--------------------------------

### SQL: Assert a database object does not exist

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Asserts that a specified database object does not exist in the database. This is useful for verifying that deprecated objects have been removed or that unintended objects were not created. The assertion fails if the object is found. Dependencies: tSQLt framework.

```sql
CREATE PROCEDURE MyTests.[test old deprecated table is removed]
AS
BEGIN
  -- Execute cleanup script
  EXEC dbo.RemoveDeprecatedObjects;

  -- Verify old table no longer exists
  EXEC tSQLt.AssertObjectDoesNotExist
    @ObjectName = 'dbo.OldCustomerTable',
    @Message = 'Deprecated table should be removed';
  -- Fails if object still exists
END;
GO
```

--------------------------------

### Fake Function with Fixed Data Source

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

This snippet demonstrates how to use tSQLt.FakeFunction to replace a system function (like GETDATE()) with a fake one that returns data from a specified table. This is useful for testing time-sensitive logic with predictable dates. It ensures that the mocked function returns '2024-12-25' when dbo.GetCurrentDate() is called within the test.

```sql
CREATE PROCEDURE MyTests.[test order validation uses current date]
AS
BEGIN
  -- Create table with test data to return from faked function
  CREATE TABLE #FakeCurrentDate (CurrentDate DATE);
  INSERT INTO #FakeCurrentDate VALUES ('2024-12-25');

  -- Replace GETDATE() function with fake returning fixed date
  EXEC tSQLt.FakeFunction
    @FunctionName = 'dbo.GetCurrentDate',
    @FakeDataSource = '#FakeCurrentDate';

  -- Now GetCurrentDate() returns '2024-12-25' instead of actual date
  DECLARE @ValidationResult BIT;
  EXEC @ValidationResult = dbo.ValidateOrderDate @OrderDate = '2024-12-24';

  EXEC tSQLt.AssertEquals
    @Expected = 0,
    @Actual = @ValidationResult,
    @Message = 'Order date before current date should be invalid';

  -- Original function automatically restored after test
END;
GO
```

--------------------------------

### SQL: Assert a database object exists

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Asserts that a specified database object (e.g., table, view, index, stored procedure) exists in the database. This is often used after deployment scripts or migrations to ensure objects were created as expected. The assertion fails if the object is not found. Dependencies: tSQLt framework.

```sql
CREATE PROCEDURE MyTests.[test migration creates customer index]
AS
BEGIN
  -- Execute migration script
  EXEC dbo.ApplyMigration_CreateCustomerIndex;

  -- Verify index was created
  EXEC tSQLt.AssertObjectExists
    @ObjectName = 'dbo.Customer.IX_Customer_Email',
    @Message = 'Customer email index should exist after migration';
  -- Fails if object doesn't exist in database
END;
GO
```

--------------------------------

### Run Tests with Custom Output Formatter

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

This snippet shows how to control the output format of tSQLt test runs. tSQLt.SetTestResultFormatter can be used to change the output to formats like XML (e.g., tSQLt.XmlResultFormatter) for integration with CI/CD pipelines. It also demonstrates running tests silently with tSQLt.RunWithNullResults and resetting the formatter to the default text output.

```sql
-- Set default formatter for session
EXEC tSQLt.SetTestResultFormatter 'tSQLt.XmlResultFormatter';

-- Run tests - output in JUnit XML format
EXEC tSQLt.Run 'AcceleratorTests';

-- Example XML output:
-- <?xml version="1.0" encoding="UTF-8"?>
-- <testsuites>
--   <testsuite tests="5" failures="0" errors="0" time="0.156" name="AcceleratorTests">
--     <testcase classname="AcceleratorTests" name="test ready for experimentation if 2 particles" time="0.031"/>
--     <testcase classname="AcceleratorTests" name="test we are not ready for experimentation if there is only 1 particle" time="0.028"/>
--   </testsuite>
-- </testsuites>

-- Run silently (no output)
EXEC tSQLt.RunWithNullResults 'AcceleratorTests';

-- Reset to default text formatter
EXEC tSQLt.SetTestResultFormatter 'tSQLt.DefaultResultFormatter';

```

--------------------------------

### tSQLt Test Skipping Directives

Source: https://github.com/tsqlt-org/tsqlt/blob/main/Build/ReleaseNotes.txt

Directives used to control test execution based on SQL Server version or specific skip reasons. These are comments processed by the tSQLt framework.

```sql
--[@tSQLt:MinSqlMajorVersion](@MinVersion) <-- Skips the test if the major version of SQL Server is < @MinVersion
--[@tSQLt:Skip](@SkipReason)               <-- Skips the test and reports @SkipReason as reason in the output
```

--------------------------------

### Apply Specific Constraint to Faked Table in T-SQL

Source: https://context7.com/tsqlt-org/tsqlt/llms.txt

Shows how to use tSQLt.ApplyConstraint to reintroduce a specific constraint onto a faked table. This allows testing the behavior of individual constraints, like foreign keys, in isolation after the table has been faked. It helps verify constraint enforcement logic.

```sql
CREATE PROCEDURE AcceleratorTests.[test foreign key violated if Particle color is not in Color table]
AS
BEGIN
  -- Fake both tables to remove all constraints
  EXEC tSQLt.FakeTable 'Accelerator.Particle';
  EXEC tSQLt.FakeTable 'Accelerator.Color';

  -- Reapply specific constraint you want to test
  EXEC tSQLt.ApplyConstraint
    @TableName = 'Accelerator.Particle',
    @ConstraintName = 'FK_ParticleColor';

  -- Now the FK is active and should prevent invalid inserts
  DECLARE @err NVARCHAR(MAX);
  SET @err = 'No exception occurred';

  BEGIN TRY
    INSERT INTO Accelerator.Particle (ColorId) VALUES (7);  -- No matching Color
  END TRY
  BEGIN CATCH
    SET @err = ERROR_MESSAGE();
  END CATCH

  -- Verify the foreign key constraint was enforced
  IF (@err NOT LIKE '%FK_ParticleColor%')
  BEGIN
    EXEC tSQLt.Fail 'Expected FK_ParticleColor violation, got:', @err;
  END;
END;
GO
```