Alter Stored Procedure in SQL AZURE

Absence of object browser in SQL Azure throws many challenges. For example, how would you update a stored procedure in your SQL Azure database? The challenge is to get the source code from the cloud database.

select * from sys.objects

will only display the objects in your database and not the source code for any stored procedure or view etc.

Here is my hack to get the source code for any stored procedure.

1. SELECT * from information_schema.Routines

Will give you the list of all stored procedures. For example you want to alter the stored procedure usp_Test. Then

SELECT routine_definition from information_schema.Routines where specific_name ='usp_Test'

Now copy the contents of routine_definition – this is the source code for usp_Test. In my case this is

CREATE PROCEDURE usp_Test AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select count(*) as TotalVisits1 from visits END

3. format this code

CREATE PROCEDURE usp_Test
AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select count(*) as TotalVisits1 from visits
END

4. change CREATE PROCEDURE to ALTER PROCEDURE

ALTER PROCEDURE usp_Test
AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select count(*) as TotalVisits1 from visits
END

Update the source code as required and execute the TSQL

5. This will update your stored procedure in the cloud database.

I guess, the best practice would be to mantain a local copy of your database and keep it in sync with your cloud database. That way we can run the code on our local copy and just run the script against the cloud database.

Oh! Well – we are just learning and I did not have a local copy of my test database. Again, this is not an elegent solution but it does work, so if you can recommend / find any better solution – please let us know…Thanks.


abhi
http://www.zimbatech.com

Views: 48

Comment

You need to be a member of Azure User Groups to add comments!

Join Azure User Groups

Latest Activity

Profile IconKevin Rolnick, Tony Williams, Mark Sullivan and 48 more joined Azure User Groups
Sep 21, 2018
chandupatla joined Mike Holdorf's group
Thumbnail

Dallas Azure User Group

Dallas Azure User Group - Meetings held at the Microsoft Technology Center in Las Colinas
Apr 20, 2017
Anthony.Tex.M posted a status
"We are ACTIVE on http://www.hccug.org ... and open for new members!"
Feb 21, 2017
Ani Arth posted an event
Thumbnail

Live! 360 Orlando at Loew's Royal Pacific Resort

November 17, 2014 to November 21, 2014
Live! 360 brings together five conferences, and the brightest minds in IT and Dev, to explore leading edge technologies and conquer current ones. These co-located events will incorporate knowledge transfer and networking, along with out-of-this-world education and training, as you create your own custom conference, mixing and matching sessions and workshops to best suit your needs. Whether you are a developer who uses Visual Studio, SQL Server, and SharePoint;…See More
Jun 6, 2014

© 2019   Created by Azure Admin.   Powered by

Badges  |  Report an Issue  |  Terms of Service