This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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;
