Lesson Learned #401:Creating Dynamic Views from Multiple Tables using Stored Procedure

[Jose_Manuel_Jurado]

Recently, I encountered an intriguing scenario where our customer needed to dynamically create views based on multiple tables, allowing them to efficiently filter and aggregate data. The challenge arose when dealing with varying amounts of data, which required careful consideration for performance optimization. Through this article, I would like to share my insights and present a stored procedure that offers additional options to address this dynamic view creation requirement effectively.

Script Overview:
The "SearchAndView" stored procedure accepts several input parameters that define the behavior and structure of the dynamic view to be created. Let's take a closer look at each parameter and its significance:

1. @SearchField: This parameter specifies the field on which the search operation will be performed within the tables. If provided, the dynamic view will only include rows that match the given criteria.

2. @SearchText: This parameter holds the search text that will be matched against the @SearchField. It complements the search functionality and allows for targeted data retrieval.

3. @TableList: This parameter contains a comma-separated list of tables from which data will be retrieved and combined in the dynamic view. By specifying the desired tables, users can tailor the view to their specific needs.

4. @TableSchema: This parameter defines the schema under which the tables reside. It ensures the correct retrieval of table data within the specified schema context.

5. @ViewSchema: This parameter represents the schema under which the dynamic view will be created. If the schema doesn't exist, the script dynamically creates it to accommodate the view.

6. @ViewName: This parameter defines the name of the dynamic view. It should be unique within the specified @ViewSchema and serves as an identifier for accessing the combined data.

7. @FieldList: An optional parameter, @FieldList, allows users to specify a list of fields they want to include in the dynamic view. If not provided, all fields from the tables will be included by default.

8. @TopCount: Another optional parameter, @TopCount, determines the maximum number of rows to include in the dynamic view. By setting this value, users can control the amount of data returned and improve query performance.

9. @AddUniqueField: This binary parameter, when set to 1, adds an additional column called "UniqueField" to each row of the dynamic view. The value of this field is generated using the NEWID() function and ensures uniqueness within the view's result set.

Script Execution:

The "SearchAndView" stored procedure follows a structured workflow to create the dynamic view. Here's an overview of the execution steps:

1. Parameter Validation: The script first validates the input parameters, ensuring that essential values like @TableSchema, @ViewSchema, @ViewName, and @TableList are provided. If any required parameter is missing, an error is raised, and the procedure is terminated.

2. Schema Creation: The script checks if the specified @ViewSchema exists. If not, it dynamically creates the schema using the CREATE SCHEMA statement. This step ensures that the view is created within the desired schema context.

3. View Existence Check: Before creating the dynamic view, the script verifies if a view with the same @ViewName already exists within the specified @ViewSchema. If found, the existing view is dropped to avoid conflicts during view creation.

4. Dynamic View Creation: The script constructs the CREATE VIEW statement using the specified @ViewSchema, @ViewName, and other parameters. It then iterates over the tables provided in @TableList, generating SELECT statements for each table. The statements include the necessary JOIN or UNION ALL operations to combine data from multiple tables.

5. Optional Functionality: If @FieldList is provided, it is included in the SELECT statement to specify the desired fields in the dynamic view. If @TopCount is specified, a TOP clause is added to limit the number of rows returned. If @AddUniqueField is set to 1, the NEWID() function is used to generate a unique identifier column, which is included in each row of the dynamic view.

6. Dynamic SQL Execution: The constructed SQL statement is executed using sp_executesql to create the dynamic view within the specified @ViewSchema and @ViewName.

7. Error Handling: The script includes error handling logic within a TRY...CATCH block. If any error occurs during view schema creation, dropping the existing view, or creating the dynamic view, an error message is raised, providing details about the encountered error.

CREATE PROCEDURE SearchAndView (
    @SearchField VARCHAR(100),
    @SearchText VARCHAR(100),
    @TableList VARCHAR(MAX),
    @TableSchema VARCHAR(100),
    @ViewSchema VARCHAR(100),
    @ViewName VARCHAR(100),
    @FieldList VARCHAR(MAX) = NULL,
    @TopCount INT = NULL,
    @AddUniqueField BIT = 0
)
AS
BEGIN
    -- Check if TableSchema is empty
    IF @TableSchema IS NULL OR LTRIM(RTRIM(@TableSchema)) = ''
    BEGIN
        RAISERROR('Error: TableSchema is required.', 16, 1)
        RETURN;
    END

    -- Check if ViewSchema is empty
    IF @ViewSchema IS NULL OR LTRIM(RTRIM(@ViewSchema)) = ''
    BEGIN
        RAISERROR('Error: ViewSchema is required.', 16, 1)
        RETURN;
    END

    -- Check if ViewName is empty
    IF @ViewName IS NULL OR LTRIM(RTRIM(@ViewName)) = ''
    BEGIN
        RAISERROR('Error: ViewName is required.', 16, 1)
        RETURN;
    END

    -- Check if TableList is empty
    IF @TableList IS NULL OR LTRIM(RTRIM(@TableList)) = ''
    BEGIN
        RAISERROR('Error: TableList is required.', 16, 1)
        RETURN;
    END

    DECLARE @SQLStatement NVARCHAR(MAX)
    DECLARE @ErrorMessage NVARCHAR(4000)

    -- Check if the view schema exists, if not, create it
    IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @ViewSchema)
    BEGIN
        SET @SQLStatement = 'CREATE SCHEMA ' + QUOTENAME(@ViewSchema)

        BEGIN TRY
            EXEC sp_executesql @SQLStatement
        END TRY
        BEGIN CATCH
            -- Handle any errors that occurred during execution
            SET @ErrorMessage = ERROR_MESSAGE()
            RAISERROR('Error occurred while creating the view schema. Error message: %s', 16, 1, @ErrorMessage)
            RETURN
        END CATCH
    END

    -- Check if the view already exists and drop it if necessary
    IF EXISTS (SELECT * FROM sys.views WHERE name = @ViewName AND SCHEMA_NAME(schema_id) = @ViewSchema)
    BEGIN
        SET @SQLStatement = 'DROP VIEW ' + QUOTENAME(@ViewSchema) + '.' + QUOTENAME(@ViewName)

        BEGIN TRY
            EXEC sp_executesql @SQLStatement
        END TRY
        BEGIN CATCH
            -- Handle any errors that occurred during execution
            SET @ErrorMessage = ERROR_MESSAGE()
            RAISERROR('Error occurred while dropping the existing view. Error message: %s', 16, 1, @ErrorMessage)
            RETURN
        END CATCH
    END

    -- Create the view
    SET @SQLStatement = 'CREATE VIEW ' + QUOTENAME(@ViewSchema) + '.' + QUOTENAME(@ViewName) + ' AS '

    -- Split the table list into separate table names
    DECLARE @Tables TABLE (RowNumber INT IDENTITY(1,1), TableName VARCHAR(100))
    INSERT INTO @Tables (TableName)
    SELECT value FROM STRING_SPLIT(@TableList, ',')

    -- Check if FieldList is empty, if so, assign default value '*'
    IF @FieldList IS NULL OR LTRIM(RTRIM(@FieldList)) = ''
        SET @FieldList = '*'

    -- Generate the SELECT statement for each table
    DECLARE @Counter INT = 1
    DECLARE @NumTables INT = (SELECT COUNT(*) FROM @Tables)
    WHILE @Counter <= @NumTables
    BEGIN
        SET @SQLStatement += 'SELECT '

        IF @TopCount IS NOT NULL
        BEGIN
            SET @SQLStatement += 'TOP(' + CAST(@TopCount AS NVARCHAR(10)) + ') '
        END

        IF @AddUniqueField = 1
        BEGIN
            SET @SQLStatement += 'NEWID() AS UniqueField, ' + @FieldList
        END
        ELSE
        BEGIN
            SET @SQLStatement += @FieldList
        END

        SET @SQLStatement += ' FROM ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME((SELECT TableName FROM @Tables WHERE RowNumber = @Counter))

        IF @SearchField <> '' AND @SearchText <> ''
        BEGIN
            SET @SQLStatement += ' WHERE ' + QUOTENAME(@SearchField) + ' = ''' + @SearchText + ''''
        END

        IF @Counter < @NumTables
            SET @SQLStatement += ' UNION ALL '

        SET @Counter += 1
    END

    -- Execute the dynamic SQL statement
    BEGIN TRY
        EXEC sp_executesql @SQLStatement
    END TRY
    BEGIN CATCH
        -- Handle any errors that occurred during execution
        SET @ErrorMessage = ERROR_MESSAGE()
        RAISERROR('Error occurred while creating the view. Error message: %s', 16, 1, @ErrorMessage)
    END CATCH
END

How to call the stored procedure 

BEGIN TRY
    EXEC SearchAndView 'name', 'John',  'Table1,Table2,Table3','dbo','MyView', 'MyView','Age,Name',Null,1
END TRY
BEGIN CATCH
    -- Handle any errors that occurred during execution
    PRINT 'Error occurred while executing the stored procedure.'
    PRINT 'Error message: ' + ERROR_MESSAGE()
END CATCH

WITH cte(AgeMax, Number)
as 
(
select MAX(AGE) as AgeMax,COUNT(*) as Number from [MyView].[MyView] 
)
select AgeMax, Number from cte 

Tables and data example

-- Create EnglishNames table
CREATE TABLE EnglishNames (
    Name varchar(100)
);

-- Insert English names into EnglishNames table
INSERT INTO EnglishNames (Name)
VALUES
    ('John'), ('William'), ('James'), ('Charles'), ('George'),
    ('Thomas'), ('Joseph'), ('Robert'), ('Michael'), ('David'),
    ('Richard'), ('Daniel'), ('Paul'), ('Mark'), ('Donald'),
    ('Edward'), ('Steven'), ('Brian'), ('Kevin'), ('Jason'),
    ('Jeffrey'), ('Scott'), ('Kenneth'), ('Anthony'), ('Eric'),
    ('Stephen'), ('Timothy'), ('Patrick'), ('Gregory'), ('Matthew'),
    ('Andrew'), ('Christopher'), ('Jonathan'), ('Ronald'), ('Dennis'),
    ('Jerry'), ('Gary'), ('Frank'), ('Raymond'), ('Peter'),
    ('Samuel'), ('Charles'), ('Henry'), ('Larry'), ('Jose'),
    ('Albert'), ('Arthur'), ('Ryan'), ('Carl'), ('Roger'),
    ('Benjamin'), ('Wayne'), ('Billy'), ('Walter'), ('Harry'),
    ('Howard'), ('Philip'), ('Stephen'), ('Vincent'), ('Jeremy'),
    ('Ralph'), ('Sean'), ('Johnny'), ('Bobby'), ('Louis'),
    ('Eugene'), ('Randy'), ('Russell'), ('Gerald'), ('Russell'),
    ('Keith'), ('Philip'), ('Franklin'), ('Alan'), ('Willie'),
    ('Roy'), ('Jesse'), ('Frederick'), ('Dustin'), ('Victor'),
    ('Glenn'), ('Alfred'), ('Leonard'), ('Melvin'), ('Lee'),
    ('Eddie'), ('Reginald'), ('Bill'), ('Wayne'), ('Martin'),
    ('Craig'), ('Dale'), ('Randall'), ('Bradley'), ('Jesus'),
    ('Leroy'), ('Curtis'), ('Warren'), ('Clarence'), ('Jerome'),
    ('Lewis'), ('Don'), ('Nathan'), ('Calvin'), ('Keith');

-- Create Table1
CREATE TABLE Table1 (
    Name varchar(100),
    Age int
);

-- Insert random data into Table1 using English names
DECLARE @Counter INT = 1;

WHILE @Counter <= 10000
BEGIN
    INSERT INTO Table1 (Name, Age)
    SELECT 
        Name,
        ABS(CHECKSUM(NEWID())) % 100 + 1 -- Random age between 1 and 100
    FROM 
        EnglishNames
    ORDER BY NEWID();

    SET @Counter += 1;
END;

-- Create Table2
CREATE TABLE Table2 (
    Name varchar(100),
    Age int
);

-- Insert random data into Table2 using English names
SET @Counter = 1;

WHILE @Counter <= 10000
BEGIN
    INSERT INTO Table2 (Name, Age)
    SELECT 
        Name,
        ABS(CHECKSUM(NEWID())) % 100 + 1 -- Random age between 1 and 100
    FROM 
        EnglishNames
    ORDER BY NEWID();

    SET @Counter += 1;
END;

-- Create Table3
CREATE TABLE Table3 (
    Name varchar(100),
    Age int
);

-- Insert random data into Table3 using English names
SET @Counter = 1;

WHILE @Counter <= 10000
BEGIN
    INSERT INTO Table3 (Name, Age)
    SELECT 
        Name,
        ABS(CHECKSUM(NEWID())) % 100 + 1 -- Random age between 1 and 100
    FROM 
        EnglishNames
    ORDER BY NEWID();

    SET @Counter += 1;
END;