Hi,
i am looking for a possible MySqL query which will check to see if a stored procedure exists on the database server, if it does great Return, if it doesnt then i can insert it using c#.
any help is appreciated
Vade
A:
On MS SQL you can perform the following query:
if exists
(
select name from sysobjects
where name = 'function_name' and type = 'fn'
)
begin
drop function function_name
end
go
Oliver
2010-08-12 10:45:21
A:
You can do this:
SELECT * FROM `information_schema`.`ROUTINES` where specific_name = 'my_procedure_name' and routine_schema = 'my_schema'
..and if it exists, should get a result. However, keep in mind that on the majority of shared hosting mysql services, routines, triggers and so on are not normally allowed to be created. If it's your own server, no problem ;)
danp
2010-08-12 10:48:02
will this still work if the prodecure is an insert one? (reason behind the insert procedure is, i need the last_inserted_id() and for some un beknown reason in my c# app using odbc drivers it will not return a result for last_inserted_id() so the procedure does it for me)
Vade
2010-08-12 11:27:13
Ah, do you mean a trigger? This still applies if the procedure is called after or before an insert, but I haven't got the syntax to hand. Regardless, if the trigger/procedure is defined, it will be in the information schema, which is available to users in pretty much all circumstances (I've certainly been always able to query it).
danp
2010-08-12 18:48:29