Updating a stored procedure

03-Sep-2019 17:56

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.Here’s the (broken) drop create pattern: -- Delete the stored procedure if it already exists if exists ( select * from sys.objects where name = N'p_My Proc' and type = N'P' ) begin drop procedure p_My Proc end go -- Now create it again create procedure p_My Proc as begin print 'go forth and do great things' end go -- And set permissions grant execute on p_My Proc to Some Role go There’s nothing wrong with this code.Just kick all your users off the server, switch to single user mode, execute the code above and it will work fine. You’re running a service that needs to be highly available, and you can’t take a maintenance period every time you want to change code?I assume that similar tricks will work for My SQL and Postgre SQL, and would love to hear from users of those platforms about their experiences.Update (11/5/2011): It turns out that My SQL doesn’t support atomic updates of stored procedures; apparently this is a long-standing bug, first filed in 2005 and still not fixed ( And Postgre SQL and Oracle both do properly support this feature with a different SQL syntax: “CREATE OR REPLACE PROCEDURE”.var microsoft = microsoft

Unfortunately, this doesn’t work if you’re trying to run a high-availability service.

When I try to create a procedure for other purpose like selecting or deleting, it works with no problems.

client tool, especially when the stored procedure is complex.

In this chapter, we will use stored procedures for CUD (create, update, delete) operations for an entity when we call the First, you need to update your existing EDM to add these stored procedures into your EDM.

Right click on your designer and click Update Model from Database..

Unfortunately, this doesn’t work if you’re trying to run a high-availability service.

When I try to create a procedure for other purpose like selecting or deleting, it works with no problems.

client tool, especially when the stored procedure is complex.

In this chapter, we will use stored procedures for CUD (create, update, delete) operations for an entity when we call the First, you need to update your existing EDM to add these stored procedures into your EDM.

Right click on your designer and click Update Model from Database..

Using the drop create method there is obviously a small window of time when the stored procedure does not exist.