i have sql server 2008 r2 database stored procedure may or may not exist.
if run sp_help spthing
, returns row if existed (name: spthing, owner: dbo, type: stored procedure).
if try create new stored procedure same name, error "there object named 'spthing' in database".
but
if run select object_id('dbo.spthing')
, returns null.
if run exec spthing
, says "could not find stored procedure 'spthing'".
if run drop spthing
, says "cannot drop procedure 'spthing', because not exist or not have permission".
if run select * sys.objects name = 'spthing'
, no row.
if run select * information_schema.routines routine_name = 'spthing'
, no row.
all these run same connection, administrative user.
does stored procedure exist?
edit:
the sp trivial select, along lines of:
create procedure spthing @param int begin select strthing tblthing lngparam = @param; end
all works fine -
if object_id ('dbo.spthing') not null drop procedure dbo.spthing go select object_id('dbo.spthing') go create procedure dbo.spthing @param int begin select strthing dbo.tblthing lngparam = @param end go select object_id('dbo.spthing')
output -
----------- null (1 row(s) affected) ----------- 664283184 (1 row(s) affected)
Comments
Post a Comment