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

Why you need to do so? What are you trying to do really? Seems like dropping a view and created again everytime isn't a good idea. — Ilyes
– Ilyes, Commented Jan 17, 2019 at 10:17
@Sami hi i didn't execute the delete after all, i am trying to create\alter a view with parameters that i will get from a user. — talR
– talR, Commented Jan 17, 2019 at 10:31
you must the pass value not variable (@PressName1) in create query — Pugal
– Pugal, Commented Jan 17, 2019 at 10:38
View's can't have variables in their definition. Are you wanting those variables to be converted to literal values in the VIEW's definition? — Thom A
– Thom A ♦, Commented Jan 17, 2019 at 10:39

Damien_The_Unbeliever · Accepted Answer · 2019-01-17 10:41:50Z

Views don't take parameters. If you had wanted to pass parameters at the point in time at which the view is used, you would define a table-valued user defined function instead.

However, here it looks like you're trying to set the parameters separately from where the view is used. Here, the best option may be some form of "parameter table" for the view, something like:

CREATE TABLE UV_filteredLogins_parms (
    Lock char(1) not null,
    constraint CK_UV_filteredLogins_Locked CHECK (Lock = 'X'),
    constraint PK_UV_filteredLogins PRIMARY KEY (Lock),
    PressType nvarchar(50),
    TestName nvarchar(50),
    PressName nvarchar(50),
    Phase nvarchar(50)
)

Now, the above table can either contain 0 rows or 1 row. I suggest that you just insert one row and now make your stored procedure issue an UPDATE against it instead.

Now, inside the view, you can simply join to this table and then compare these column values against the columns from the other tables¹. You don't need to be dropping and re-creating it whenever the parameter values change.

I have no idea why @Phase was being marked as an OUTPUT.

¹Either CROSS JOIN and continue to do the actual comparisons in the WHERE or an INNER JOIN and move some/all of the comparisons into it's ON clause.

Thom A · Accepted Answer · 2019-01-17 12:26:19Z

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.

Pugal · Accepted Answer · 2019-01-17 11:05:34Z

0

I guess, you want this SP.

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

AS
--Failure Report Table
begin
 DECLARE @ViewDROP nvarchar(MAX) = N'DROP VIEW [dbo].[UV_filteredLogins]' -- 'N' has been added
 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= '''+@Phase +''' and press.PressName= '''+ @PressName +''' 
  and pressType.Type_Description= '''+@PressType +
  /*Parameter's values are applied in WHERE condition*/
  ''' 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
  )';


  EXEC sp_executesql @STMT              -- View will be created.
  select * from UV_filteredLogins       -- Call it.
  --EXEC sp_executesql @ViewDROP        -- Drop query of view.
end

exec dbo.MTBFAlterView 'HP Indigo 10000', 'Go Green'
, 'MR-193','Test'
-- If you call this, then view is created and followed by 'SELECT'

edited Jan 17, 2019 at 11:05

answered Jan 17, 2019 at 10:57

Pugal

5495 silver badges21 bronze badges

3 Comments

Thom A Over a year ago

Please consider injection when writing dynamic SQL. This is wide open to it.

Pugal Over a year ago

@Larnu.. I don't have knowledge about injection. so let me study about the injection.

Thom A Over a year ago

I certainly recommend reading up about Injection; making your (dynamic) SQL safe is an invaluable skill.

Collectives™ on Stack Overflow

How to exec a create view with variables

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related