Recently I installed Sql server 2012, From that time On word me facing a issue, When trying to open stored procedure in new query window i am getting multiple line breaks in between due to this it is very difficult to go through the Code Please help me to resolve the issue.
Please find the code snap shot.
What I have tried:
--SP_HELPTEXT SP_GetSummaryReport
--EXEC SP_GetSummaryReport_bkp_CR_2976_114218_12_12_2018 0,'','','302719','','Open','',0,'',''
-- =============================================
-- Author: Sarang Darkonde
-- Create date: 11-May-2016
-- Description: SP_GetSummaryReport
-- =============================================
CREATE PROCEDURE [VRMS].[SP_GetSummaryReport]
(
@ApplnID int = null,
@FromDate varchar(50) = null,
@ToDate varchar(50) = null,
@TsgHeadID varchar(20) = null,
@BTGHeadId varchar(20) = null,
@status varchar(5) = null,
@ResidualRisk varchar(10)=null,
@Tier int=null,
@Sox varchar(3)=null,
@Type varchar(10)=null
)
AS
BEGIN
declare @sql varchar(8000);
set @sql = ' select
CONVERT(int,ROW_NUMBER() over (order by rptmain.Report_Id)) as srno,
applnmst.Application_Name,
rptmap.URL,
applnmst.Tier,
applnmst.SOX_Criticality,Observation,rptmap.Residual_Rating as Severity,rptmap.Inherent_Rating as Inherent_Risk,rptmap.Residual_Rating as Residual_Risk,
-- below code added by Prathamesh as per requirement as on 28/11/2016--
isnull(applnmst.Tech_Group_Name,''--'') as Tech_Group_Name,
applnmst.Bt_Head_Name,
isnull(applnmst.RRR_Approvers_in_IRIS_Name,''--'') as TsgHead_Name,
applnmst.AO_Name,
applnmst.CAN_ID,
datediff(DD,rptmap.Reported_Dt,GETDATE()) as DAYSOPEN,CAST( Closed_Dt AS DATE) as Closed_Dt,
Exp_Closure_Dt as Expected_Clos_Dt,
rptmain.Remarks,
--code ends here--
Reported_Dt as RptDt,
Due_Date as Due_Date,
rptmap.Active_Flag as Status,
Reported_By as Rptby,
case when rptmap.Type = ''P'' then ''Cloud based IP''
when rptmap.Type = ''I'' then ''Internal''
when rptmap.Type = ''U'' then ''Cloud based URL''
when rptmap.Type = ''V'' then ''Annual VAPT''
when rptmap.Type = ''A'' then ''ASLC''
when rptmap.Type = ''R'' then ''Recursive''
when rptmap.Type = ''B'' then ''BitSight''
else
''''
end as Type,
obsmst.Category,rptmap.Status_Flag as Action,
Exp_Closure_Dt as Expected_Clos_Dt,rptmap.ISG_Status_Flag,applnmst.Lifecycle_Stage,Group_company
from VRMS_APPLICATION_MASTER as applnmst,
VRMS_OBSERVATION_MASTER as obsmst,
VRMS_OBS_REPORT_MAIN as rptmain,
VRMS_OBS_REPORT_MAPPING as rptmap
where
applnmst.Application_Id = rptmain.Application_Id
and rptmain.Report_Id = rptmap.Report_Id
and rptmap.Observation_Id = obsmst.Observation_Id'
if (@ApplnID <>'' AND @ApplnID is not null AND @ApplnID <> 0)
Begin
set @sql = @sql + ' and rptmain.Application_Id= '+ convert(varchar(10),@ApplnID) + ''
End
if (@TsgHeadID <>'' AND @TsgHeadID is not null AND @TsgHeadID <> 0)
Begin
set @sql = @sql + ' and applnmst.RRR_Approvers_in_IRIS_Id='''+ @TsgHeadID +''''
End
if (@BTGHeadId <>'' AND @BTGHeadId is not null)
Begin
set @sql = @sql + ' and applnmst.BT_Head ='''+ @BTGHeadId +''''
End
if (@FromDate <>'' and @ToDate <>'')
begin
set @sql = @sql + ' and convert(datetime,DATEADD(dd, 0, DATEDIFF(dd, 0, rptmap.Reported_Dt)),103) > = convert(datetime,'''+@FromDate+''', 103)
and convert(datetime,DATEADD(dd, 0, DATEDIFF(dd, 0, rptmap.Reported_Dt)),103) < = convert(datetime,'''+@ToDate+''', 103)'
end
if (@status <>'' AND @status is not null) --AND @status <> 0)
Begin
set @sql = @sql + ' and rptmap.Active_Flag = '''+ @status +''''
End
if (@ResidualRisk <>'' AND @ResidualRisk is not null) --AND @status <> 0)
Begin
set @sql = @sql + ' and rptmap.Residual_Rating = '''+ @ResidualRisk +''''
End
if (@Tier <>'' AND @Tier is not null AND @Tier <> 0)
Begin
set @sql = @sql + ' and applnmst.Tier = '+ convert(varchar(10),@Tier) + ''
End
if (@Sox <>'' AND @Sox is not null) --AND @status <> 0)
Begin
set @sql = @sql + ' and applnmst.SOX_Criticality = '''+ @Sox +''''
End
if (@Type <>'' AND @Type is not null) --AND @status <> 0)
Begin
set @sql = @sql + ' and rptmain.Type = '''+ @Type +''''
End
print (@sql);
exec(@sql);
END
Submit an article or tip