为什么我在SQL server Management Studio 2012中尝试更改存储过程时会出现多个换行符 [英] Why I am getting multiple line breaks when I am trying to alter stored procedure in SQL server management studio 2012
本文介绍了为什么我在SQL server Management Studio 2012中尝试更改存储过程时会出现多个换行符的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
最近我安装了Sql server 2012，从那时起我就面临一个问题，当我试图在新的查询窗口中打开存储过程时，我之间会有多个换行符，因此很难通过代码请帮我解决这个问题。
请找代码快照。
我尝试过：
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
推荐答案
使用sp_HelpText时，默认值是将结果发送到网格，每列也有256个字符的限制。
您可以使用查询菜单通过将此更改为结果To =>来稍微改变这一点结果到文本。然后再次在查询菜单中选择查询选项，文本，并将每列的最大字符数增加到8192.
有一些代码可用于创建一个没有这些限制的更好版本的sp_HelpText。 新的SQL Server sp_helptext以避免线路分裂代码 [ ^ ]
我自己只需右键单击程序名称并选择脚本存储过程 as 创建
When usingsp_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
避免使用 sp_helptext 处理现有的存储过程，因为它可以在默认情况下添加换行符256 char限制并将其拆分为多行，如@MadMyche所述。
正确使用尝试使用 sys.sql_modules 视图，或者通过右键单击存储过程并选择修改或脚本存储过程来生成SSMS中的代码。
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".
这篇关于为什么我在SQL server Management Studio 2012中尝试更改存储过程时会出现多个换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
