There is a well-known issue in SQL Server Management Studio 2012 with sp_helptext: It puts extra lines in grid result set. This problem is known by the Microsoft since 2011 and it seems that they really do not want to fix it until the next version of SQL Server.
I will show how to reproduce the problem and how to make a workaround for it.
I: Reproduce the problem:
1: Open SQL Server Management Studio 2012
2: Create a sample stored procedure:
3: Run SP_HelpText with Results to Grid mode:
- Make sure that Results to Grid option is set:
- Run sp_helptext:
- Copy the resultset, and paste into the code window:
As we can se, as soon as we pasted the result into our code window, each line got an extra row.
II. Possible solutions:
- Most famous on the internet: use the Results to Text (CTRL-T) option and copy from there.
- Create our own version from the sp_helptext stored procedure.
- Switch to SSMS 2008R2
- Wait until the next version of SQL Server is released.
* Extra solution: use regexp replace on the copied result (we just have to replace all \n\n patterns to \n
I think there is no really good solution for the problem: Using Results to Text option means that we always have to switch between the text and grid mode during our work. As a database engineer I think that is only waste of time. We can create an own version of sp_helptext. The advantage is that we can use it really easy and we have a full control on the solution (that is why I have chosen this method. The disadvantage is that we have to compile our custom procedure in each database that we use. Of course we can switch to the older version of Management Studio too but I am not sure backward upgrading can be a proper solution for anything, and of course we can wait until the next release but if we work with it every day we must have a fix as soon as possible. And of course, we can use regexp replace but who has such time?
III. Let se usp_helptext
After I decided to create a custom version, I sat down and created the following script:
As we can see, I have to learn a lot about programming sql server. This solution is based on my structured programming studies: It uses a loop and conditional statements. I made so of course it works fine but it is not a good-looking solution if your solution is set-based. It must contain some selects or it not a solution .
When I have finished with my usp_helptext procedure, I showed it to my boss. He likes challenges so immediately started to type something. When he finished, he send his solution to me and I started to test it. After I recognized all the boundary cases the transact-sql solution has born:
I am proud to this solution. It is really a set-based solution and it shows what is the difference between an usual programmer and an sql developer. The golden rule is: If we want to use a loop we just have to sit down and search a proper select statement.