AutoProcs

AutoProcs

One of the excuses for not writing SQL is that the standard operations are so mundane to write. So Insight writes them for you. AutoProcs are special scripts that the schema engine uses to automatically generate standard stored procedures for you. You can put them in your SQL scripts just like any other script. When your table changes or the primary key changes, the AutoProcs are automatically regnerated so you don't have to maintain them.

In its simplest form:

AUTOPROC All tablename
GO

Note that you can also put this in a SQL comment so your scripts are still valid SQL (although SQL server will ignore them of course):

-- AUTOPROC All tablename
GO

Types of AutoProcs

Currently Insight will generate the following procedures for you. Let's assume you have a Users table:

SelectUser - takes the primary key(s) as input and returns a match
InsertUser - inserts a single record and returns any identity or computed columns
UpdateUser - uses the primary key(s) to update all of the non-readonly columns of a given record
UpsertUser - inserts or updates a single record and returns any identity or computed columns
DeleteUser - deletes a record matching the primary key(s)
SelectUsers - selects all records given a list of keys
InsertUsers - inserts a batch of records and returns any identity or computed columns
UpdateUsers - updates a batch of records
UpsertUsers - inserts or updates a batch of records and returns any identity or computed columns
DeleteUsers - deletes all records given a list of keys
UserIdTable - the User-Defined table type used for SelectPlural and DeletePlural
UserTable - the User-Defined table type used for InsertPlural, UpdatePlural and UpsertPlural
Find - returns all records matching a set of criteria. 

Note that when naming procedures Insight changes the table name from plural to singular form (Users -> User) or singular to plural form (User -> Users), depending on whether the operation acts upon one or multiple records. Note that AutoProc Find has more details specific to Find.

Enabling Optimistic Concurrency

By default, AutoProcs don't do concurrency checking, but you can enable it by adding the Optimistic flag:

AUTOPROC All,Optimistic tablename

See Optimistic Concurrency for more details.

Generating Some, but not Other AutoProcs

If you only want to generate some of the AutoProcs, you can do that too:

AUTOPROC Select tablename
GO
AUTOPROC Insert tablename
GO

Note that these need to be in separate GO sections.

Renaming the AutoProcs

If you don't like the names that Insight generates, you can specify the names manually:

AUTOPROC Select Beer Name=Beer_SelectOne
GO
AUTOPROC Insert Beer Name=Beer_Insert
GO

You can also use templates for names. This is handy if you want to rename all of the procedures.

• {0} = Proc Type (Select, Insert, etc.)
• {1} = Table Name (original, not singularized)
• {2} = Table Name (singular or plural as appropriate)

So to rename all of the Beer procs to Beer_Select, Beer_Insert, etc.:

AUTOPROC All Beer Name={1}_{0}
GO

Note that if you get wishy-washy on the names, Insight might forget to drop the procs with old names. We'll work on that if people come across it.

Overriding the Singular Form

If the AutoProc guesses the singular form of your table wrong, you can override that:

AUTOPROC All Foo Single=Fo

Yes, the singular form of 'Foo' is 'Fo'.

Overriding the Plural Form

If the AutoProc guesses the plural form of your table wrong, you can override that:

AUTOPROC All Foo Plural=Fot

Yes, the plural form of 'Foo' is 'Fot'.

Execute as Owner

Some of the AutoProcs (e.g. Find) use dynamic SQL to execute the commands. Your security model may require that these procs execute as the owner of the procedure. If you want to enable that, use the ExecuteAsOwner=true option

AUTOPROC All [Beer] ExecuteAsOwner=true

Official Syntax for AutoProcs

If you use multiple options, they must be in the following order:

AUTOPROC [TypeEnum] [TableName] [Name=name] [Single=name] [Plural=name] [ExecuteAsOwner=true]