Anytimescripts

Anytime Scripts

Functions/Views/Sprocs/etc

Anytime scripts are scripts that are run anytime they have changes. That means RH automatically detects new files and changs in files and runs when it finds changes.

How should I structure my scripts?

There are two methods to structure your scripts.

1. Drop/Create - this methodology will destroy permissions, but is easier to implement. If you are using this method, be aware that you will be dropping any item level permissions and will need to reapply them after you run. What does it look like with SQL Server?

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ss.usp_GetAllThis')) drop procedure ss.usp_GetAllThis
GO
Print 'Creating ' + @Type + ' ' + @Schema + '.' + @Name

CREATE PROCEDURE [ss].[usp_GetAllThis] 
/* your procedure guts here */

1. Create If Not Exists / Alter - this method is better for making non-permission destructive changes. What does that look like?

/* This is the create if not exists part */
DECLARE @Schema varchar(20), @Name VarChar(100), @Type VarChar(20)
SET @Schema = 'ss' 
SET @Name = 'usp_GetAllThis'
SET @Type = 'PROCEDURE'

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@schema +'.' +  @Name))
BEGIN
  DECLARE @SQL varchar(1000)
  SET @SQL = 'CREATE ' + @Type + ' ' + @Schema + '.' + @Name + ' AS SELECT * FROM sys.objects'
  EXECUTE(@SQL)
END 
Print 'Updating ' + @Type + ' ' + @Schema + '.' + @Name
GO

/* Then all you do is set up alter like below */

ALTER PROCEDURE [ss].[usp_GetAllThis] 
/* your procedure guts here */

Related

See OneTimeScripts
See EveryTimeScripts