Click here to Skip to main content
13,863,289 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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 
Posted
Updated 11-Feb-19 21:05pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

When using sp_HelpText, the default is to send the results to a grid and there is also a limit of 256 characters per column.

You can go use the Query menu to somewhat change this by changing this to Results To => Results to Text. Then again in the query menu and choose Query Options, Text and increase the maximum characters per column up to 8192.

There is code available to create a better version of sp_HelpText which does not have these limitations. New SQL Server sp_helptext to avoid line splits of code[^]

I myself just right click on the procedure name and choose to Script Stored Procedure as Create
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Avoid using sp_helptext to work on an existing Stored Procedure, as it can add line line breaks post the default 256 char limit and split it into multiple lines, as mentioned by @MadMyche.

For correct usage try using sys.sql_modules view, or generate code in SSMS by right clicking the Stored Procedure and choose either "Modify" or "Script Stored Procedure".
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.190214.1 | Last Updated 12 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100