Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
IN choose script options you can make "Include if NOT EXISTS" to False –  kumar_2002 Jan 17 '13 at 19:58
    
Several tools will do it for you. I would just buy a tool and save precious time. –  AlexKuznetsov Jan 17 '13 at 21:49
    
@AAron, I was using a scalar function as an example. This script(template) would apply to other objects as well. –  Chris L Jan 18 '13 at 14:25
    
I kind of agree with @AlexKuznetsov. If you are going to write scripts to cover a variety of objects, and you are going to be doing this a lot, and you are not doing this solely for educational purposes, I would go spend a few hundred bucks on a tool that will do this with a few clicks, and will have already worked out many bugs you haven't even created yet. This is so prevalent I've blogged about it: madelinebertrand.com/2012/04/20/… –  Aaron Bertrand Jan 18 '13 at 14:32
add comment

1 Answer 1

up vote 3 down vote accepted

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?

share|improve this answer
    
That looks like it will work. Thanks for the help, and yea, SQL Server really should have Create or Replace functionality. –  Chris L Jan 17 '13 at 22:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.