if object_id('tempdb..#database_details') is not null
DROP TABLE #database_details
SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[the_procedure_name]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
DROP PROCEDURE the_procedure_name
Alternatively, the following code will CREATE a dummy SP if it doesn't exist, then ALTER it. This way you will only CREATE, not DROP, which can come in handy in certain circumstances, since it will save permissions. Note that this uses INFORMATION_SCHEMA, which is more portable than sysobjects.
IF NOT EXISTS
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'the_routine_name'
and routine_type = 'PROCEDURE' --could also be 'FUNCTION' for a function
EXEC ('CREATE PROC dbo.the_procedure_name AS SELECT 1')
ALTER PROCEDURE dbo.the_procedure_name