SQL Server: T-SQL Alternative to 'CREATE OR REPLACE' in PL/SQL

'CREATE OR REPLACE' SQL statement is used widely in PL/SQL (supported in Oracle, PostgreSQL, etc). It is very useful in SQL script files which help user to build up database objects.

But in T-SQL (supported in Microsoft SQL Server only), 'CREATE OR REPLACE' SQL statement is not supported. Till SQL Server 2012 being published, this statement is not supported yet. But if you use 'Create procedure <procedure_name>' statement, you cannot execute for multiple times. As PL/SQL 'CREATE OR REPLACE' can be executed for multiple times, T-SQL 'Create procedure <procedure_name>' seems not make sense.

However, can we improve the user experience of 'Create procedure <procedure_name>'? Yeah, of course we could.

Here is an example to show alternative of 'CREATE OR REPLACE' in T-SQL:

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS(select * from sys.procedures where name = 'sp_LinkMessage')EXEC sp_ExecuteSQL N'CREATE PROC dbo.sp_LinkMessage as RAISERROR(''dbo.sp_LinkMessage is incomplete'', 16, 127);';GO-- =============================================-- Author:Yaping Xin-- Create date: 2012.6.11-- Description:link command message to function-- Modification: -- =============================================ALTER PROCEDURE dbo.sp_LinkMessage @idFunction numeric(18,0),@idMessage numeric(18,0)ASBEGIN-- TODO --ENDGO