SQL Prompt - encapsulate as new stored procedure

In this tip Data Platform MVP Ike Ellis explains how you can refactor SQL code quickly by creating a new stored procedure from within an existing stored procedure, all using SQL Prompt.

Transcript:
Hi I'm Ike Ellis and I'm going to show you something cool I found with Redgate SQL Prompt. One of things you can do is if you're in a stored procedure and you want to take some of the code, like a little bit of the logic, and you want to create a different stored procedure. like for instance I’m getting data from the customers table and the orders table but I'm doing both by the customer ID, which is a parameter being handed in to the stored procedure.

What you can do with SQL Prompt is you can pull the SQL Prompt menu down and you can say ‘encapsulate as new stored procedure’ and it says “well what name do you want to give it?”. I want to give it GetOrders and what you'll find is it took the get orders stored procedure and it found the dependency on this ID and added it as a parameter here and then it took your code and encapsulated it a new stored procedure. So when you look at the script you can see that it looks kinda exactly how I would write it if I was going to write it by hand. But the other thing it did is it went back to your original stored procedure and it replaced that code with the call to the stored procedure you just created.

That's just a really cool way of refactoring. We've had that ability to do it in Visual Studio for a long time and it's great that SQL Prompt brought this into Management Studio so that we could do it with transact SQL. Hopefully you think that's a cool tip and you download SQL Prompt and try it out for yourself. Thanks have a great day.