| Author |
 Topic Search  Topic Options
|
dj air 
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Delete Stored Procedure via SQL
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. 
im making an update script and want to delete all existing SP's then re add the new ones.
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 04 January 2005 at 11:27am |
|
Try 'Drop Procedure name'
Replace name with the stored procedures name.
|
|
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
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
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
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 upgrades work.
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
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
|
|
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 January 2005 at 6:29am |
cheers michael, thats what i want, 
they have a prefix. so that will help.
ill have ago with that. cheers again.
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
