So when writing scripts to create tables you might often include some SQL like below to first assess whether or not the table exists before dropping it.
IF OBJECT_ID('Schema.TableName', 'U') IS NOT NULL DROP TABLE Schema.TableName GO
This might be because you are still testing data and want a table with a different structure to be created or to exist and be populated with different data while using the same table name. So it’s the right thing to do in that circumstance but you may be wrongly carrying that thinking forward into creating stored procedures and user defined functions.
By dropping an SP or UDF you are also breaking any securities or permissions associated with that SP or UDF meaning these permissions etc. will have to be created again.
But for SPs and UDFs you don’t actually need to drop the SP or UDF you just want to change it if it exists. In which case you use Alter rather than Create. However what if you are unaware as to whether the SP or UDF actually exists?
I propose creating dummy SP’s or UDF’s in their place which will simply be over written as demonstrated below.
-- ALTER STORED PROCEDURE -- THIS DUMMY SP JUST SELECTS 1 IF OBJECT_ID('[Schema].[NameOfStoreProcedure]') IS NULL EXEC ('CREATE PROCEDURE [Schema].[NameOfStoreProcedure] AS SELECT 1') GO ALTER PROCEDURE [Schema].[NameOfStoreProcedure] @DateParameter DATE ,@IntParameter INT ,@CharParameter VARCHAR(30) AS BEGIN ------------------------------------------------------ ------------------------------------------------------ --INSERT STORED PROCEDURE LOGIC HERE --E.G. SELECT 1 ------------------------------------------------------ ------------------------------------------------------ END; GO -- ALTER FUNCTION -- THIS DUMMY UDF SIMPLY SETS THE PARAMETER @INT TO 1 IF OBJECT_ID('[Schema].[NameOfFunction]') IS NULL EXEC (' CREATE FUNCTION [Schema].[NameOfFunction] (@INT AS INT) RETURNS INT AS BEGIN SELECT @INT = 1 RETURN @INT END ') GO ALTER FUNCTION [Schema].[NameOfFunction] (@INT AS INT) RETURNS INT AS BEGIN SET @INT = @INT + 1 RETURN @INT END GO -- RUN FUNCTION TO SEE RESULT SELECT [Schema].[NameOfFunction](10) AS ReturnedValue
Tidbytez 