How to insert multiple begin end inside stored procedure-CodePudding

I realized that I cannnot use GO inside stored procedure.

I have multiple processes where I need to drop view and then create view etc.

How do I create a stored procedure without getting error?

Basically, this is not a nested process, but process just goes from top to bottom because I am dropping views, creating views then later creating tables.

Bottom is basic structure of two processes, but I will have at least 10 processes with similar pattern.

CREATE PROCEDURE Create_Views_Tables_Nov2021

AS
BEGIN
set nocount on

begin
    begin
        drop view [dbo].[vw_tblSage_GLDETAIL_0001] 
    end

begin
    CREATE VIEW [dbo].[vw_tblSage_GLDETAIL_0001]  
    AS
    SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_1_Stage]
    union 
    SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_2_Stage]
end

end

CodePudding user response：

BEGIN...END aren't batch separators. T-SQL doesn't have a built in batch separator (GO is not part of T-SQL). If you want to run the statements in their own batches, you'll have to use deferred statements by executing them with sys.sp_executesql.

CREATE PROCEDURE dbo.Create_Views_Tables_Nov2021

AS
BEGIN
    SET NOCOUNT ON;

    EXEC sys.sp_executesql N'DROP VIEW [dbo].[vw_tblSage_GLDETAIL_0001];';

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13)   NCHAR(10);
    SET @SQL = N'CREATE VIEW [dbo].[vw_tblSage_GLDETAIL_0001]'   @CRLF  
               N'AS'   @CRLF  
               N'SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_1_Stage]'   @CRLF  
               N'UNION --Should this not be UNION ALL?'   @CRLF  
               N'SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_2_Stage];'

    EXEC sys.sp_executesql @SQL;

END

CodePudding user response：

If you run into a problem that seems to be a catch 22, this is a good indication that your using the wrong approach.

Instead of dropping and recreating your view, you should instead be using:

ALTER VIEW vw_tblSage_GLDETAIL_0001
AS
SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_1_Stage]
union 
SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_2_Stage]

to change your view.

If you can't be sure that the view already exists before hand (it's always good to make this assumption), then use:

CREATE OR ALTER VIEW vw_tblSage_GLDETAIL_0001
AS
SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_1_Stage]
union 
SELECT *  FROM [dbo].[tblSage_GLDETAIL_1_2_Stage]