How to exec a create view with variables

Question

I am trying to execute a view every time to procedure run. what is the problem with my code? why it doesn't work?

CREATE PROCEDURE dbo.MTBFAlterView @PressType nvarchar(50), @TestName nvarchar(50), @PressName nvarchar(50), @Phase nvarchar(50)
AS
--Failure Report Table
DECLARE @ViewDROP nvarchar(MAX) = 'DROP VIEW [dbo].[UV_filteredLogins]'
DECLARE @ParmDefinition nvarchar(500);  
 DECLARE @STMT AS NVARCHAR(MAX) = N'
                                    Create VIEW [dbo].[UV_filteredLogins]
                                    as
                                    SELECT logins.[ID]
                                        ,[Test_ID]
                                        ,phase.Phase_Name
                                        ,press.PressName
                                        ,pressType.Type_Description as PressType
                                        ,[Operator]
                                        ,[LoginDate]
                                        ,[LogoutDate]
                                        ,DATEDIFF(MINUTE,LoginDate,LogoutDate) as TimeDiff
                                    FROM [TDM_Analysis].[dbo].[Logins] as logins join [TDM_Analysis].[dbo].[Presses] as press on logins.Press_ID=press.ID 
                                    join [TDM_Analysis].[dbo].[Phases] as phase on logins.Phase_ID=phase.ID 
                                    join [TDM_Analysis].[dbo].[PressTypes] as pressType on pressType.ID=press.PressType_ID
                                    join [TDM_Analysis].[dbo].[Tests] as test on logins.Test_ID=test.ID
                                    where phase.Phase_Name= @Phase1 and press.PressName= @PressName1 and pressType.Type_Description=@PressType1 and [Test_ID]=TestName1 and logoutDate is not null
                                        and Operator in (SELECT au.Email
                                                        FROM [UsersAuthorization].[dbo].[RolesMembers] as RM join [UsersAuthorization].[dbo].[ApplicationUsers] as AU on RM.ApplicationUserID=au.ID
                                                        where rm.roleid=1)';
    SET @ParmDefinition=N'@PressType1 nvarchar(50), @TestName1 nvarchar(50), @PressName1 nvarchar(50), @Phase1 nvarchar(50) OUTPUT'; 
    --EXEC sp_executesql @ViewDROP
   EXEC sp_executesql @STMT, @ParmDefinition, @PressType1 = @PressType, @TestName1=@TestName, @PressName1=@PressName, @Phase1=@Phase OUTPUT;



   exec dbo.MTBFAlterView @PressType='HP Indigo 10000', @TestName='Go Green', @PressName='MR-193', @Phase='Test'

my result is: Msg 156, Level 15, State 1, Line 34 Incorrect syntax near the keyword 'VIEW'.

Answer 1

If you want the parameters you're passing to be literatl values in your VIEW's definition, then this is the way you want to build it:

CREATE PROCEDURE dbo.MTBFAlterView @PressType nvarchar(50), @TestName nvarchar(50), @PressName nvarchar(50), @Phase nvarchar(50)
AS BEGIN

    IF EXISTS (SELECT 1 FROM sys.objects WHERE [name] = 'UV_filteredLogins')
        DROP VIEW UV_filteredLogins;

    DECLARE @SQL nvarchar(MAX) = N'
CREATE VIEW [dbo].[UV_filteredLogins]
AS
    SELECT logins.[ID],
           [Test_ID],
           phase.Phase_Name,
           press.PressName,
           pressType.Type_Description AS PressType,
           [Operator],
           [LoginDate],
           [LogoutDate],
           DATEDIFF(MINUTE, LoginDate, LogoutDate) AS TimeDiff
    FROM [TDM_Analysis].[dbo].[Logins] logins
         JOIN [TDM_Analysis].[dbo].[Presses] press ON logins.Press_ID = press.ID
         JOIN [TDM_Analysis].[dbo].[Phases] phase ON logins.Phase_ID = phase.ID
         JOIN [TDM_Analysis].[dbo].[PressTypes] pressType ON pressType.ID = press.PressType_ID
         JOIN [TDM_Analysis].[dbo].[Tests] test ON logins.Test_ID = test.ID
    WHERE phase.Phase_Name = ' + QUOTENAME(@Phase,N'''') + N'
      AND press.PressName = ' + QUOTENAME(@PressName,N'''') + N'
      AND pressType.Type_Description = ' + QUOTENAME(@PressType,N'''') + N'
      AND [Test_ID] = TestName1
      AND logoutDate IS NOT NULL
      AND Operator IN (SELECT AU.Email
                       FROM [UsersAuthorization].[dbo].[RolesMembers] RM
                            JOIN [UsersAuthorization].[dbo].[ApplicationUsers] AU ON RM.ApplicationUserID = AU.ID
                       WHERE RM.roleid = 1);';

    EXEC sp_executesql @SQL;
END
GO

Note the use of QUOTENAME here. That keeps your dynamic SQL safe, by quoting the values appropriately when generating the values. So, for example, a value like "don't" would be parsed as 'don''t'. This avoids Injection in your SP, which is really important when using dynamic SQL.