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'.
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.