How do I check that a stored procedure exists using ADO.Net? Is there a specific way to do this (apart from executing SELECT OBJECT_ID() and checking the result)?
+2
A:
You can use the INFORMATION_SCHEMA views like so:
Select *
From INFORMATION_SCHEMA.ROUTINES
Where ROUTINE_NAME = '<your procedure name>'
Thomas
2010-04-28 02:57:33
Is there a predefined method in ADO.Net to do this?
HeavyWave
2010-05-02 11:25:03
@HeavyWire - No there is no built-in method to ADO.NET to determine the existence of a stored procedure. You must build a query that you call against SQL Server to determine if a procedure exists.
Thomas
2010-05-02 13:59:43
A:
If you're asking whether or not there's a way to check without using any SQL at all, then the answer is no.
You could use SQL Management Objects if you really want a code-only solution, but that's a pretty heavy dependency to add simply to check for the existence of a stored procedure - and it's just going to issue SQL behind the scenes anyway.
It's best if you simply use a SqlCommand.
Aaronaught
2010-04-28 02:59:11
A:
Or if you don't care about adhering to SQL standard constructs (INFORMATION_SCHEMA), you can use the built-in SQL Server system catalog view (2005 and up) to check:
SELECT object_id, name
FROM sys.procedures
WHERE name = 'your-procedure-name-here'
marc_s
2010-04-28 05:21:47