How to detect if a Sproc exists ?

Hi,

How can I detect if a stored procedure exists in my database with ado.net ?

Stan

Frans
Sat Nov 22 05:53:09 CST 2003

"Stan Sainte-Rose" <stan@cyber972.com> wrote in news:eqzVl0OsDHA.3468
@TK2MSFTNGP11.phx.gbl:

> Hi,
>
> How can I detect if a stored procedure exists in my database with ado.net
?

Query the system tables in Master (sqlserver 7) or use a query on
INFORMATION_SCHEMA.ROUTINES to see if the stored proc is there. (Sqlserver
2000).

There is also a way to determine this via OleDb, using the
OleDbConnection.GetOleDbSchemaTable() method with the proper parameters.

Frans

--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com

Miha
Sat Nov 22 07:08:09 CST 2003

As Frans stated there is GetOleDbSchemaTable.
Example:
oleDbConnection1.Open();

DataTable schemaTable =
oleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures,

new object[] {null, null, "Sales by Year"});

oleDbConnection1.Close();

If "Sales by Year" stored procedure exists, schemaTable will have adjacent
record. Otherwise schemaTable will be empty.


--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"Stan Sainte-Rose" <stan@cyber972.com> wrote in message
news:eqzVl0OsDHA.3468@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> How can I detect if a stored procedure exists in my database with ado.net
?
>
> Stan
>
>

William
Sat Nov 22 13:49:46 CST 2003

Like Frans and Miha Mention, I'd use Information_Schema as the preferred
method, but you can use the ADO.NET version as well.

Just as an additional method you can use the good old fashioned
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[usp_myProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

"Stan Sainte-Rose" <stan@cyber972.com> wrote in message
news:eqzVl0OsDHA.3468@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> How can I detect if a stored procedure exists in my database with ado.net
?
>
> Stan
>
>

Joe
Sat Nov 22 22:21:45 CST 2003

strSQL = "SELECT Count(*) FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[MySproc]') and xtype IN (N'FN', N'IF', N'TF')"

NumRecs = CInt(.ExecuteScalar(strSQL))
If NumRecs = 1 Then
'Sproc exists.
--
Joe Fallon


"Stan Sainte-Rose" <stan@cyber972.com> wrote in message
news:eqzVl0OsDHA.3468@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> How can I detect if a stored procedure exists in my database with ado.net
?
>
> Stan
>
>