SQL DB Modification Templates


This post is just a place for me dump some regularly used templates for doing CRUD operations on DB code.

I create these little tidbits at work and then forget to take them with me. So whenever I start a new job, I have to recreate them. This time I’m going to start recording them here.

CREATE OR ALTER PROCEDURE

-- replace values for _schema_, _procname_, _params_, _body_, _user_, and _asuser_. Then delete this line.
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 1/3: Check for existing version starting';
IF OBJECTPROPERTY(OBJECT_ID('[_schema_].[_procname_]'), 'IsProcedure') IS NULL
BEGIN
  PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 1b: Creating stub';
  EXECUTE ('CREATE PROCEDURE [_schema_].[_procname_] AS RAISERROR(''The ALTER PROCEDURE FOR [_schema_].[_procname_] failed'', 16, 1);');
END
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 1/3: Check for existing version completed';

PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 2/3: Altering Stored Procedure starting';
GO
ALTER PROCEDURE [_schema_].[_procname_]
                 _params_
AS
BEGIN
_body_
END; /* [_schema_].[_procname_] */
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 2/3: Altering Stored Procedure completed';
  
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 3/3: Granting Permissions starting';
GRANT EXECUTE
    ON OBJECT::[_schema_].[_procname_] TO [_user_]
    AS [_asuser_];
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 3/3: Granting Permissions completed';
GO

CREATE TEMP TABLE

-- replace values for _temptablename_ and _columns_. Then delete this line.
IF OBJECT_ID('[tempdb]..[#_temptablename_]') IS NOT NULL
    TRUNCATE TABLE [#_temptablename_]
ELSE
    CREATE TABLE #[_temptablename_]
    ( _columns_
    );

There are more to add… when I have to create another, I’ll add it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s