I am trying to write a stored procedure which will be checking create and modify dates of view (sys.views) and external table (sys.external_tables) and recreate the view when needed (so only on the last modified date). And I am struggling a bit with writing as I am new to this.

So far I have:

CREATE PROCEDURE create_alter_view 
(
    @Name VARCHAR(50) = 'table_1'
)
AS 
BEGIN
    IF ( 0 <> (SELECT create_date, modify_date FROM sys.external_tables WHERE modify_date = MAX(modify_date)))
        BEGIN
        DECLARE @statement NVARCHAR(MAX) = N'CREATE OR ALTER VIEW '   @Name   ' AS
        SELECT TOP (10) *
        FROM
        FROM [dataverse_progrescloud_unqff756b2e147e462a957b87128de15].[dbo].[progres_individual]
        ) AS [r]
'       
        EXEC sp_executesql @tsql = @statement;
    END
END

Would someone be kind to help with my logic and code here? I would really appreciate it!

CodePudding user response：

You have a number of glaring errors in your procedure

• You are comparing 0 to two columns from a subquery, both of which are dates, which makes no sense at all. A subquery must select a single value
• The subquery itself doesn't make a huge amount of sense. You probably want to compare the modify_date of sys.external_tables and sys.views.
• The data types for object names is wrong.
• To re-create the view, you probably want a dynamic query that gets the definition and runs an ALTER.

CREATE PROCEDURE create_alter_view 
    @Name sysname = 'table_1'
AS

SET NOCOUNT ON;

DECLARE @definition nvarchar(max);

SELECT
  @definition = REPLACE(sm.definition, 'CREATE', 'ALTER')
FROM sys.views v
JOIN sys.sql_modules sm ON s.object_id = v.object_id
WHERE v.name = @Name
  AND EXISTS (SELECT 1
    FROM sys.sql_expression_dependencies ed
    JOIN sys.external_tables et ON et.name = ed.referenced_entity_name
    WHERE ed.referencing_id = v.object_id
      AND et.modify_date > v.modify_date
);

IF @definition IS NOT NULL
BEGIN
    EXEC sp_executesql @definition;
END

However, I recommend you do not use the above. It is very brittle, as it's subject to you not having the word CREATE elsewhere in the view, and sql_expression_dependencies can also be problematic if you do not declare the schema explicitly.

It appears what you actually want is sp_refreshsqlmodule, which is designed to refresh views and procedures when the underlying tables have been changed.

CREATE PROCEDURE create_alter_view 
    @Name sysname = 'table_1'
AS

SET NOCOUNT ON;

IF EXISTS (SELECT 1
    FROM sys.views v
    WHERE v.name = @Name
      AND EXISTS (SELECT 1
        FROM sys.sql_expression_dependencies ed
        JOIN sys.external_tables et ON et.name = ed.referenced_entity_name
        WHERE ed.referencing_id = v.object_id
          AND et.modify_date > v.modify_date
    )
EXEC sp_refreshsqlmodule @Name;