View/Stored Procedure details being cut off

[expired user #8437]'s profile image [expired user #8437] posted 9 years ago in General Permalink
When I click on a view or stored procedure to see what code makes up the item. The statement in the window is being cut off allot. Is this a know issue or is there a solution?

Thank you
igitur's profile image igitur posted 9 years ago Permalink
I experience this too on MS SQL. For example, I have this stored procedure script (from MS SQL Server Management Studio):

CREATE proc [dbo].[sel_NHRdetail_bak] @Scheme_ID as integer, @ValDte datetime = null  AS  
--declare @SchID as integer, @ValDte datetime; select @SchID = 9  
if @ValDte is null  
select @ValDte = MAX(ValDte) from vNHR h  
select scheme_id, schemename, CloseDte, ValDte, d  
, grossup, NetUp, nhr, nhr_nacc, Increase  
, nhrcol, Weight, tPrice  
, YEPrice, yeclosedte, YEDte, PriceSrc, YEPriceSrc--distinct l.DSSCode, l.DSSFactor, l.NHRCol, 'Valuedate' = @ValDte, v.tPrice, v.Reason, v.PriceSrc, v.CloseDte, v.PrvDte  
, GrossUp_, NetUp_, dpsfact, divval, HldVal, divfact, GrossupFact, totalcharges  
from vNHR v  
where v.ValDte = @ValDte   
and v.scheme_id = @Scheme_ID  
GO


If I manually run
EXEC sp_helptext 'dbo.sel_NHRdetail_bak'
in HeidiSQL I see the exact above script in the result set, but if I select the stored procedure in HeidiSQL and view the Procedure tab, I get this in the CREATE code tab:

CREATE PROCEDURE "sel_NHRdetail_bak"()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
from vNHR h  
select scheme_id, schemename, CloseDte, ValDte, d  
, grossup, NetUp, nhr, nhr_nacc, Increase  
, nhrcol, Weight, tPrice  
, YEPrice, yeclosedte, YEDte, PriceSrc, YEPriceSrc--distinct l.DSSCode, l.DSSFactor, l.NHRCol, 'Valuedate' = @ValDte, v.tPrice, v.Reason, v.PriceSrc, v.CloseDte, v.PrvDte  
, GrossUp_, NetUp_, dpsfact, divval, HldVal, divfact, GrossupFact, totalcharges  
from vNHR v  
where v.ValDte = @ValDte   
and v.scheme_id = @Scheme_ID


and this in the Routine body textbox:
from vNHR h  
select scheme_id, schemename, CloseDte, ValDte, d  
, grossup, NetUp, nhr, nhr_nacc, Increase  
, nhrcol, Weight, tPrice  
, YEPrice, yeclosedte, YEDte, PriceSrc, YEPriceSrc--distinct l.DSSCode, l.DSSFactor, l.NHRCol, 'Valuedate' = @ValDte, v.tPrice, v.Reason, v.PriceSrc, v.CloseDte, v.PrvDte  
, GrossUp_, NetUp_, dpsfact, divval, HldVal, divfact, GrossupFact, totalcharges  
from vNHR v  
where v.ValDte = @ValDte   
and v.scheme_id = @Scheme_ID


Do you use regex or something to parse the result from sp_helptext ?
igitur's profile image igitur posted 9 years ago Permalink
For this script, the Routine body textbox is completely empty, but I get a message saying "You have no privilege to this routine", which is incorrect. My guess is the parsing of the script throws an error?

[code sql]
CREATE procedure [dbo].[sel_RPPOutput]
@Portfolio_ID INT, @Scheme_ID int = null, @ValueDate datetime
AS
select * from RPPOutput
where Portfolio_ID = @Portfolio_ID
and Scheme_ID = @Scheme_ID
and ValueDate = @ValueDate

GO
[/code]

Please login to leave a reply, or register at first.