How to create an object and then alter it

Question

I'm using sql server 2012:

I would like to script out creation of an object(if it doesn't exist first), and then alter it.

I'm trying to avoid the usual drop/create as it would impact stats. It also has to do with how we're scripting out changes to the DB.

What I have:

Go
If object_id(N'My_Function', N'FN') IS NULL 
Begin
Create Function My_Function
(
    @SomeParameter int
)
Returns int
As
Begin
    -- Intentionally empty
End

 End

Go
-- A header/comments
Alter Function My_Function
(
@SomeParameter int
)
Returns int
As
Begin
Declare @returnValue int
    -- A bunch of sql code
Return @returnValue
End
Go

The problem I'm having is "Create" must be first in the script(or first after a "GO"), but I can't put a "GO" inside an "If" conditional. Is there a way to conditionally create something if it doesn't exist, and then alter it later?

Answer 1

You can't put CREATE FUNCTION inside IF logic since it needs to be in its own batch.

I would do it this way. If the function doesn't exist yet, create an empty stub. Now you can always safely issue an alter.

IF OBJECT_ID('dbo.function_name') IS NULL
BEGIN
  DECLARE @sql NVARCHAR(MAX) = N'CREATE FUNCTION dbo.function_name()
    RETURNS INT 
    AS
    BEGIN
      RETURN (1);
    END';

  EXEC sp_executesql @sql;
END

ALTER FUNCTION dbo.function_name
(
  ... more complex logic here ...

Unfortunately I know of no easy way to get SSMS to script it out this way for you, and even doing that through SMO or other methods is going to be a pain IMHO. Simply because the function has to be of the same type (IF, TVF, MSTVF), so your simple stub can't always be the same.

Wouldn't it be great if SQL Server supported CREATE OR REPLACE?