Print Page | Close Window

Delete Stored Procedure via SQL

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=13206
Printed Date: 29 March 2026 at 9:20am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Delete Stored Procedure via SQL
Posted By: dj air
Subject: Delete Stored Procedure via SQL
Date Posted: 04 January 2005 at 10:17am
hi guys,.

does anyone know if you can delete a Stored Procedure using a query. like alter, add a stored procedure. i can't find it anywhere. devguru, didn't help weirdly. or i can't see it.LOL

im making an update script and want to delete all existing SP's then re add the new ones.



Replies:
Posted By: WebWiz-Bruce
Date Posted: 04 January 2005 at 11:27am
Try 'Drop Procedure name'

Replace name with the stored procedures name.


-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting


Posted By: Mart
Date Posted: 04 January 2005 at 11:35am
If you are using SQL Server (it sounds like you are) you can let enterprise manager do an upgrade script for you and write all of that repetitive code


Posted By: dj air
Date Posted: 04 January 2005 at 12:34pm
yea is SQL server.

i want a asp page like MS_Server_setup.aspwhich will upgrade my V2 application to V3. add a few fields etc that ok.

i want ed to delete all existing SP's and add the new ones, thanx boRg ill try that.


Posted By: Mart
Date Posted: 04 January 2005 at 12:52pm
One way to do it is to open up a V2 database in enterprise manager and create a new database diagram.... then using the diagram add all the columns and alter everything that changed between the V2 database and V3... then you just click Generate Update Script on the toolbar or something like that and it will script all the changes for you. At least thats how http://linkmanager.devjunkies.com - http://linkmanager.devjunkies.com upgrades work.


Posted By: michael
Date Posted: 04 January 2005 at 10:43pm
If your sp's have a certain prefix and you want to make sure all are gone before you re-create them you can also run some query like
Declare @prc varchar(150)
While (Select Count(*) from sysobjects where name Like 'djair_%' and xtype = 'P') > 0
Begin
 Set @prc = (Select 1 [name] from sysobjects where name LIKE 'djair_%' and xtype='P')
 Set @prc = 'Drop Procedure ' + @prc
 Exec(@prc)
END


-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker


Posted By: dj air
Date Posted: 05 January 2005 at 6:29am
cheers michael, thats what i want, Wink

they have a prefix. so that will help.

ill have ago with that. cheers again.


Posted By: dj air
Date Posted: 05 January 2005 at 4:52pm
hi michael, not sure whats going on here, might be my mistake

do i need to create a stored procedure  to delete the others or would something like this work.

        'delete all stored procedures
        strSQL = "Declare @prc varchar(150) "
        strSQL = strSQL & "While (Select Count(*) from sysobjects where name Like '" & strSPPrefix & "%' and xtype = 'P') > 0 "
        strSQL = strSQL & "Begin "
         strSQL = strSQL & "Set @prc = (Select 1 [name] from sysobjects where name LIKE '" & strSPPrefix & "%' and xtype='P') "
        strSQL = strSQL & "Set @prc = 'Drop Procedure ' + @prc "
        strSQL = strSQL & " Exec(@prc) "
        strSQL = strSQL & "END "


        'Write to the database
        adoCon.Execute(strSQL)

because i have tried the above and when it goes to do it, it just hangs, there are a few Stored procedures but not as many as trhe forum.

thanx in advanced,

its hanging now, been a couple minutes.


Posted By: Mart
Date Posted: 05 January 2005 at 4:56pm
Thats how you should run it, not as a procedure... let me try it out on my local server in query analyser debug mode


Posted By: Mart
Date Posted: 05 January 2005 at 5:02pm
Looks good for me, try doing a Response.WRite(strSQL) to check you copied it correctly


Posted By: dj air
Date Posted: 05 January 2005 at 5:12pm
weird, the page wont load now,
just hangs al to gether, but a friend  said it works on hers.. weird

the whole domain is hanging, even when not in the folder

http://support.emagine-hosts.com/tv/msSQL_server_setup.asp





Posted By: dj air
Date Posted: 05 January 2005 at 5:20pm
managed to get an out put,.


Declare @prc varchar(150) While (Select Count(*) from sysobjects where name Like 'BTSPTemp%' and xtype = 'P') > 0 Begin Set @prc = (Select 1 [name] from sysobjects where name LIKE 'BTSPTemp%' and xtype='P') Set @prc = 'Drop Procedure ' + @prc Exec(@prc) END


BTSPTemp is the prefix of the SP's still just hangs


Posted By: Mart
Date Posted: 05 January 2005 at 5:32pm
you kept the % in, right?


Posted By: Mart
Date Posted: 05 January 2005 at 5:33pm
If you put the prefix name in incorrectly (i.e. no procedures begin with it in the current database) it will effectively be an eternal loop


Posted By: dj air
Date Posted: 05 January 2005 at 6:11pm
cheers,

ill check to make sure the procedures are existing.

also regarding " you kept the % in, right?"

the above statement is what i have as the query


Posted By: dj air
Date Posted: 05 January 2005 at 7:12pm
the procedures are on the server, and with the same prefix as above.


Posted By: dj air
Date Posted: 06 January 2005 at 1:30pm
ive fixed it. the code was written wrong, made a concinuous loop it was ment to be.


'delete all stored procedures
strSQL = "Declare @prc varchar(150) "
strSQL = strSQL & "While (Select Count(*) from sysobjects where name Like '" & strSPPrefix & "%' and xtype = 'P') > 0 "
strSQL = strSQL & "Begin "
strSQL = strSQL & "Set @prc = (Select MIN([name]) from sysobjects where name LIKE '" & strSPPrefix & "%' and xtype='P') "
strSQL = strSQL & "Set @prc = 'Drop Procedure ' + @prc "
strSQL = strSQL & " Exec(@prc) "
strSQL = strSQL & "END "


or set the bold to TOP 1,

before it was making a field called name with the value 1, in a loop.

thanx anyway, also let you know.Wink



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net