A Syntax Mystery in a Previously Working Procedure

  • Marwa Joseph

    SSC Journeyman

    Points: 86

    Comments posted to this topic are about the item A Syntax Mystery in a Previously Working Procedure

  • hr_sn

    Ten Centuries

    Points: 1062

    Interesting post!

    But, I'm curious why would you use sp_helptext to get the content of stored procedure? Wouldn't it be easier if just right click on SP and ask SSMS to create script for Alter or Create? I tried that and it gives me correct content, the way it should be.

  • Marwa Joseph

    SSC Journeyman

    Points: 86

    Well said! Those are other useful ways to get the contents. But it is obvious that many programmers may think that all ways give same results. For a programmer who is used to use sp_helptext may waste a lot of time diagnosing until he/she think of trying other ways.

  • craig 81366

    SSC Eights!

    Points: 808

    Personally I would rather perform my updates from a "master" script maintained in a source control system.

    You can use a VS Database project, third party equivalent, or a custom solution.

    Your database will be a reflection of what the schema is, but your source code repository is a reflection of what it "should be".

    It's extremely difficult to track what the schema "should be" across multiple databases; even if you only have only 1 prod database, you should at a minimum have separate dev, test and fail-over databases.

  • RichB

    SSCrazy Eights

    Points: 9651

    I've had similar issues before, also being an sp_helptext fan, but typically got round it by adjusting results to text and upping the output in query options.

    I suspect that some of the issue is related more to wide lines without line breaks? Quite why anyone would want to scroll horizontally when they have a perfectly good mouse wheel escapes me.

    Mind you, I have seen various bits of code sneakily appended to the end of a long line of white space so it doesn't usually show on the screen...

  • Marwa Joseph

    SSC Journeyman

    Points: 86

    If the script comes from a third part application you will not have a "master script" in your source code control.

    In that case you will have to use one of the ways highlighted to get the contents.

  • petitpere

    SSCrazy

    Points: 2520

    Thanks a lot for sharing your experience. I personally did not know/remember about sp_helptext, but I'm sure it will help me a lot in the future ...

    🙂

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1782

    Your example uses AdventureWorks2012, which suggests you're using SQL2012.

    Then why aren't you using [sys].[all_sql_modules] or [INFORMATION_SCHEMA].[ROUTINES] ???

    Creating a new custom sp_helptext seems a fruitless endeavour. I'm struggling to find something positive to say about this article. The described problem only seems to be a problem if you're using very old tooling and very old uncontrolled approaches to software development.

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    gary.strange-sqlconsumer (6/18/2015)


    Your example uses AdventureWorks2012, which suggests you're using SQL2012.

    Then why aren't you using [sys].[all_sql_modules] or [INFORMATION_SCHEMA].[ROUTINES] ???

    Creating a new custom sp_helptext seems a fruitless endeavour. I'm struggling to find something positive to say about this article. The described problem only seems to be a problem if you're using very old tooling and very old uncontrolled approaches to software development.

    The positive point of this article is that it creates an awareness of a limitation in sp_helptext. I personally never code with lines anywhere near the maximum length supported by sp_helptext but I still appreciate this being brought to my attention. I find sp_helptext much faster than a drill down in object explorer. Some of us prefer to type instead of point and click.

  • james.jensen1350

    SSChasing Mays

    Points: 640

    I'm with @hr_sn on this. There is no reason to use sp_helptext and work at getting the output correct, when you can just right-click the procedure in the Object Explorer and get entire create script in a new tab or on the clipboard. Even better, you can right-click the procedure and select Modify from the menu and you will get a MODIFY PROC script that needs no extra work to get it right.

    Really, the whole idea of these tools is to reduce the amount of work you expend to get something done. Many of the SPs I work on are somewhere between 600 to 1200 lines long and I just don't have time to spend on sp_helptext's crazy output when the better solution is only a click away.

    So, if any of you are using sp_helptext to reconstruct procedures so you can modify and rebuild them, I strongly suggest dropping that practice and get on the fast track with the right-click options.

  • Jeff Moden

    SSC Guru

    Points: 994546

    I don't know why this article only got 3 stars. I'll admit that I didn't do a deep dive on the article (I'm familiar with the problem) but the article appears to correctly identify the problem, what the symptoms and effect is, what the cause is, and what the fix is. I haven't actually tried the code but the article also provides code that anyone can use to demonstrate the problem to themselves.

    While I agree that a lot of people don't even know that sp_HelpText exists, it's a good lesson to be learned for any stored procedure output especially if you look at the history of previous posts complaining of this very problem right here on SSC.

    Good job, Marwa.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • chris.puncher

    SSC-Addicted

    Points: 433

    First of all marwa, congratulations on sticking your head over the parapet and being brave enough to post an article on SQL Server Central; sharing knowledge is how people learn.

    Having said that I think you've really solved a problem that was of your own making (MSDN lists the 255 character limitation). SSMS is there for a reason, so use it. Even the crazy world of Oracle has given in and created SQLDeveloper.

    One last point is that if you did not have the source of the SP in a VCS because it was in a third party application, then their licensing terms probably prohibit you from modifying it.

  • craig 81366

    SSC Eights!

    Points: 808

    hr_sn (6/17/2015)


    Wouldn't it be easier if just right click on SP and ask SSMS to create script for Alter or Create?

    Not really, because you've missed a few steps...

    - Expand Databases

    - Expand the specific database you're interested in

    - Expand Programmability

    - Expand Stored Procedures

    - Locate the stored procedure you're interested in.

    Only now can you "just" right click the SP.

    The above rigmarole is rarely easier than typing: sp_helptext procname and pressing Alt+X. (Hands don't even have to leave the keyboard.)

    Sometimes I don't even need to type the proc name, Ctrl+V will suffice. 😉

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    craig 81366 (6/18/2015)


    hr_sn (6/17/2015)


    Wouldn't it be easier if just right click on SP and ask SSMS to create script for Alter or Create?

    Not really, because you've missed a few steps...

    - Expand Databases

    - Expand the specific database you're interested in

    - Expand Programmability

    - Expand Stored Procedures

    - Locate the stored procedure you're interested in.

    Only now can you "just" right click the SP.

    The above rigmarole is rarely easier than typing: sp_helptext procname and pressing Alt+X. (Hands don't even have to leave the keyboard.)

    Sometimes I don't even need to type the proc name, Ctrl+V will suffice. 😉

    +1

  • DonlSimpson

    SSCertifiable

    Points: 6774

    What's surprising to me here is that no one has brought up the idea of using a source control system. sp_helptext is great if you want to see if the version of a sproc in a database is up to date, but you should be using a version control repository.

    Don Simpson



    I'm not sure about Heisenberg.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply