How to prevent an error when a stored procedure already exist

[maupiti]

Access 2003. SQL 2000 Server

Run-Time Error '-2147217900 (80040e14)':
There is already an object named 'UpdateAuthors' in the database.

How to prevent an error when a stored procedure already exist

///////////////////////////////////

Private Sub ADO_SP()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConnect As String, strSQL As String

Set cnn = New ADODB.Connection
strConnect = "Provider=sqloledb; Data Source=Win-2000-Server; Initial Catalog=Pubs; Integrated Security=SSPI;"
cnn.ConnectionString = strConnect
cnn.Open
strSQL = "CREATE PROCEDURE UpdateAuthors @state Char(2) AS " _
& "UPDATE Authors " _
& "SET state = 'FL'" _
& "WHERE state = @state"
cnn.Execute strSQL
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "{ call UpdateAuthors(?) }"
cmd.Parameters.Refresh
cmd.Parameters(0).Value = "NC"
cmd.Execute
cnn.Close
End Sub

 

[CaptainD]

In order to do what you want, you'll have to check to see if it exist (Look into using "If
Exist", if so you can drop it, then create it, but you can not have two tables in the same database with the same name.

 

[cmmrfrds]

Another way is to look at the meta data in sql server. The table sysobjects contains information on objects.

For example.

Select * from dbo.sysobjects
Where type = 'P'
This give info on stored procedures including the name.

Select * from dbo.sysobjects
Where type = 'U'
This gives information on user tables. There are lots of sys tables that contain information.

 

[PHV]

I don't know SQL Server, but perhaps this syntax is admitted ?
CREATE OR UPDATE PROCEDURE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886