How to create stored procedure with view?

Question

I tried to execute this code and it worked o.k without the the stored procedure and with it, it made an error.

The error is:

Msg 102, Level 15, State 1, Procedure UV_MTBF, Line 251 Incorrect syntax near 'Event_'.

Is the stored procedure have a limitation in length? can someone help me with my code?

edit* my problem is with ' + QUOTENAME(@category,N'''') + N' i want to add an integer from a variable that i received in the stored procedure. how can i do it?

 CREATE PROCEDURE dbo.MTBFCalculation @Category int, @Action bit, @relateToParent bit
    as 
    
    IF EXISTS (SELECT 1 FROM sys.objects WHERE [name] = '[dbo].[UV_MTBF]')
            DROP VIEW [dbo].[UV_MTBF];
    
        DECLARE @Event nvarchar(MAX) = N'
    
    CREATE VIEW [dbo].[UV_MTBF]
    as
    with failureReportTable as (SELECT [ID] as failure_id
                                      ,[Login_ID]
                                      ,[Event_ID]
                                      ,[StartDate]
                                      ,[EndDate]
                                      ,DATEDIFF(Hour,[StartDate],[EndDate]) as eventDurationMin
                                      ,[IsRelevantForBI]
                                      ,[IsParallelReport]
                                      ,[ParentReportID]
                                      ,[IsPausedEvent]
                                      ,Case 
                                            When ParentReportID>0 Then 1       --Chiled
                                            When IsParallelReport=1 Then 2     --Parent
                                            Else 3                             --not Parallel
                                            End as ParallelStatus
                                  FROM [TDM_Analysis].[dbo].[FailureReports]),
    
            fullFailure as (select *, ROW_NUMBER() OVER (ORDER BY [StartDate] ) AS IDrow
                            from failureReportTable join [TDM_Analysis].[dbo].[UV_filteredLogins] as viewLogins on failureReportTable.Login_ID=viewLogins.ID
                            WHERE event_id IN (SELECT ID  FROM [TDM_Analysis].[dbo].[Events] where EventCategory_ID=' + QUOTENAME(@category,N'''') + N')
                                and (ParallelStatus=3 or ParallelStatus=(case when ' + QUOTENAME(@relateToParent,N'''') + N'=1 then 2 else 1 end))),
    
                                    
    --------------create first failure table------------------
    
            failure_Event_1 as (select f1.failure_id as Event_1_Failure_ID
                                      ,f1.[Login_ID] as Event_1_Login_ID
                                      ,f1.[Event_ID] as Event_1_Event_ID
                                      ,f1.[StartDate] as Event_1_StartDate
                                      ,f1.[EndDate] as Event_1_EndDate
                                      ,f1.eventDurationMin as Event_1_eventDurationMin
                                      --,f1.[IsRelevantForBI] as Event_1_IsRelevantForBI
                                      --,f1.[IsParallelReport] as Event_1_IsParallelReport
                                     -- ,f1.[ParentReportID] as Event_1_ParentReportID
                                     -- ,f1.[IsPausedEvent] as Event_1_IsPausedEvent
                                      ,f1.[Test_Name] as Event_1_TestName
                                      ,f1.Phase_Name as Event_1_PhaseName
                                      ,f1.PressName as Event_1_PressName
                                      ,f1.PressType as Event_1_PressType
                                      --,f1.[Operator] as Event_1_Operator
                                      ,f1.[LoginDate] as Event_1_LoginDate
                                      ,f1.[LogoutDate] as Event_1_LogoutDate
                                      ,f1.TimeDiff as Event_1_LoginDuration
                                      ,f1.IDrow+1 as row1
                                      from fullFailure as f1),
    
    --------------create second failure table------------------
                                      
             failure_Event_2 as (select f1.failure_id as Event_2_Failure_ID
                                      ,f1.[Login_ID] as Event_2_Login_ID
                                      ,f1.[Event_ID] as Event_2_Event_ID
                                      ,f1.[StartDate] as Event_2_StartDate
                                      ,f1.[EndDate] as Event_2_EndDate
                                      ,f1.eventDurationMin as Event_2_eventDurationMin
                                    --  ,f1.[IsRelevantForBI] as Event_2_IsRelevantForBI
                                     -- ,f1.[IsParallelReport] as Event_2_IsParallelReport
                                     -- ,f1.[ParentReportID] as Event_2_ParentReportID
                                     -- ,f1.[IsPausedEvent] as Event_2_IsPausedEvent
                                      ,f1.[Test_Name] as Event_2_TestName
                                      ,f1.Phase_Name as Event_2_PhaseName
                                      ,f1.PressName as Event_2_PressName
                                      ,f1.PressType as Event_2_PressType
                                    --  ,f1.[Operator] as Event_2_Operator
                                      ,f1.[LoginDate] as Event_2_LoginDate
                                      ,f1.[LogoutDate] as Event_2_LogoutDate
                                      ,f1.TimeDiff as Event_2_LoginDuration
                                      ,f1.IDrow as row2
                                      from fullFailure as f1),
    
    ------------- join two failure tabels and calculating MTTR-mean time to repair (duration of failue), MTTF-mean time to failue( end of one until start of a new one), MTBF-mean time between failue (from start of a failure to start of a new one)--------------------
    
            joinFailures as (select *, Event_1_eventDurationMin as MTTR
                                      ,CASE
                                            When isnull(f2.row2,0)=0 then DATEDIFF(HOUR,f1.Event_1_EndDate,f1.Event_1_LogoutDate) 
                                            WHEN f1.Event_1_Login_ID=f2.Event_2_Login_ID THEN DATEDIFF(HOUR,f1.Event_1_EndDate,f2.Event_2_StartDate) 
                                            When (select TOP 1 sum(timediff) 
                                                            from [TDM_Analysis].[dbo].[UV_filteredLogins]
                                                            where logindate>f1.Event_1_LogoutDate and logindate<f2.Event_2_LoginDate) is null then DATEDIFF(HOUR,f1.Event_1_EndDate,f1.Event_1_LogoutDate)+DATEDIFF(HOUR,f2.Event_2_LoginDate, f2.Event_2_StartDate) 
                                            ELSE 
                                                     (select TOP 1 sum(timediff)+DATEDIFF(HOUR,f1.Event_1_EndDate,f1.Event_1_LogoutDate)+DATEDIFF(HOUR,f2.Event_2_LoginDate, f2.Event_2_StartDate)
                                                            from [TDM_Analysis].[dbo].[UV_filteredLogins]
                                                            where logindate>f1.Event_1_LogoutDate and logindate<f2.Event_2_LoginDate)
                                                    
                                        END AS MTTF
                            from failure_Event_1 as f1 left join failure_Event_2 as f2 on f1.row1=f2.row2),
    
            positiveJoinFailure as (select * from joinFailures where MTTF>=0)
    
    
    ---- select calculation table order by ascending time----------
    
    select * --Event_1_Failure_ID,Event_2_Failure_ID,MTTR,MTTF, MTTR+MTTF as MTFB
    from positiveJoinFailure
    --order by row1     
    ';
    
    --------------------------------------------------------Action------------------------------------------------------------------------------
    
    
    if @Action=1
    begin
    EXEC sp_executesql @Event;
    end

Answer 1

for this part of your query

where EventCategory_ID=' + QUOTENAME(@category,N'''') + N')

2 option here, you convert the value of @category to string and then concatenate with the dynamic query

where EventCategory_ID=' + convert(varchar(10), @category)

OR, you pass the value in as a parameter.

for this option, you specify @category in the dynamic query

where EventCategory_ID= @category
and (ParallelStatus=3 ....

and you pass the value in at sp_executesql

EXEC sp_executesql @Event, N'@category int', @category

By the way, Option 2 is the preferred method when using dynamic query