How to forcibly create stored procedure even if some error occure?

Question

Hi,

When i execute database script, i got errors in stored procedure then it couldn't create that stored procedure that have errors. I want to forcibly create stored procedure even if some error occur in stored procedure.

Thanks in advance

Answer 1

You can force the creation of a stored procedure by creating it in a can't-fail way. See this example:

if object_id('sp_name') is null
    exec sp_executesql N'create procedure dbo.sp_name as select 1'
go
alter procedure db.sp_name
as
...
go

Now if the alter fails, there will be a stored procedure anyway.

Answer 2

if some error occurred in the create procedure statement it won't create stored procedure. what i want is that stored procedure will be created any way either error occurred or not.

Answer 3

Actually we have database in SQL Server 2000, i attached it in SQL Server 2005 and then change the compatibility level to 90. And finally i want to execute the script but i also want that if some error occurred then it will ignore error and create the objects.