AutoProc Find

AutoProc Find

The AutoProc Find procedure is quite handy. It lets you match on any number of columns, take the top records, and sort (by up to two columns). You could write this yourself, but then you would have to write a lot of code to filter on each column in your table. And when your table changes, you'd have to update your procedures.

So just let Insight handle it for you. It will automatically regenerate the procedure if your table or primary key changes.

General Parameters

• Top = Specifies the maximum number of records to return
• OrderBy = Specifies a column to order on
• ThenBy = Specifies a second column to order on

Operators

By default, find uses exact matches for columns. So if you specify Name, you get Name=@Name. You can change the operator for a column by using the @ColumnOperator parameters:

EXEC FindBeer @OriginalGravity=0.06, @OriginalGravityOperator='>'

Find supports the following operators:

• , <, >=, <=, =, <>

• LIKE
• IS NULL, IS NOT NULL (the parameter value is then ignored)

Sorting

Use the OrderBy and ThenBy parameters to order your result set.

EXEC FindBeer @Name='%IPA%, @NameOperator='LIKE', @Top=5, @OrderBy='[OriginalGravity] DESC, @ThenBy='[Flavor]'

Notes:

• The column name must be enclosed in square brackets.
• You can sort descending by adding [DESC].
• ThenBy is ignored unless OrderBy is specified.

TOP

You can take the top records by passing in the @Top parameter.

EXEC FindBeer @Top=5, @Name='%IPA%', @NameOperator='LIKE'

Skipping/Paging

If you are on SQL Server 2012, You can skip records and implement paging really easily:

EXEC FindBeer @Top=5, @Skip=25, @Name='%IPA%', @NameOperator='LIKE'

Total Rows

If you are paging, and you want to return the total number of rows, just add a NON-NULL @TotalRows parameter, and Find will return that as well.

DECLARE @TotalRows[int] = 0
EXEC FindBeer @Top=5, @Skip=25, @Name='%IPA%', @NameOperator='LIKE', @TotalRows=@TotalRows OUTPUT