ALTER CREATE syntax for creating altering object across all versions of SQL server

Question

I have few scripts which either creates new objects like SP or view/functions and some which just ALTERS them.

Now how should I create one standard script where my script can work for above work

Example: using CREATE OR ALTER SCRIPT does not work for versions < 2016

How should I write scripts that works from SQL2012 to SQL2019 versions

Need to do drop/create. Alter won't work if it doesn't exist already. — dfundako, May 24 '21 at 21:25

score 0 · Answer 1 · answered May 24 '21 at 21:34

Something like this should work.

DECLARE @Command     VARCHAR(MAX)
        ,@ObjectSchemaName VARCHAR(MAX) = 'dbo.SomeTable'
        ,@ObjectType VARCHAR(50)  = 'TABLE';

IF EXISTS (
              SELECT    1
              FROM      sys.objects AS o
                        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
              WHERE     s.name + '.' + o.name = @ObjectSchemaName
          )
BEGIN
    SET @Command = 'ALTER ' + @ObjectType + ' AS SOME CODE HERE ';
END;
ELSE BEGIN
SET @Command = 'CREATE ' + @ObjectType + ' AS SOME CODE HERE ';
END;

SELECT @Command

ALTER CREATE syntax for creating altering object across all versions of SQL server

1 Answers1