sql server - Drop and re-create procedure if it exists in T-SQL not working -

sql server - Drop and re-create procedure if it exists in T-SQL not working -

if exists ( select * sys.objects object_id = object_id(n'msl_get_ip_location') , type in (n'p', n'pc')) drop procedure [dbo].[msl_get_ip_location] go create procedure [dbo].[msl_get_ip_location] @ip nvarchar(100) begin declare @ipnumber bigint select @ipnumber = dbo.convertip2num(@ip) select [country_code],[country_name] [mydatabase].[dbo].[ip2country] @ipnumber between ip_from , ip_to end

i have above code check if stored procedure msl_get_ip_location exists in current database. expect drop , re-create procedure if exists.

however, if procedure exists code still executing , result next error 'there object named 'msl_get_ip_location' in database.'

why isn't code failing drop procedure if exists?

the same code works procedure in same database.

try (preferred method using view):

if exists(select 1 information_schema.routines routine_name = 'prc_name' , specific_schema = 'schema_name') begin drop procedure prc_name end

or (not recommended using direct access scheme table):

if exists (select 1 sys.procedures name = 'prc_name' , schema_name(schema_id) = 'schema_name' , [type] in (n'p',n'pc')) begin drop procedure prc_name end

why first method preferred can find out illustration in question: sql server: should utilize information_schema tables on sys tables?

sql-server tsql stored-procedures