Thursday, 15 January 2009

T-SQL Alternative to Oracle's REPLACE Stored Procedure Option

When working with Oracle (11g) stored procedures it's easy to add the "OR REPLACE" option so you can keep making modifications and re-creating the code.

However no such direct alternative exists in SqlServer T-SQL (2008). So when running scripts you need to add a check before creating your code to drop it if it exists.

So, a (very basic) Oracle PLSQL stored procedure, using the OR REPLACE option, may look like

CREATE OR REPLACE PROCEDURE spTest AS
BEGIN
NULL;
END;
/

the T-SQL script alternative needs to look like

IF OBJECT_ID('dbo.spTest') IS NOT NULL
DROP PROCEDURE dbo.spTest;
GO

CREATE PROCEDURE dbo.spTest AS
BEGIN
RETURN;
END
GO

Replaced.

No comments: