Search Accounts User Interface

Search Accounts Interface Screenshot

In This Blog

TL;DR

  • sp_CRUDGen is a free open-source SQL Server stored procedure generator created by Emergent Software's Kevin Martin.

  • It automatically generates stored procedures using SQL Server metadata, foreign keys, and data types.

  • The tool supports advanced dynamic search queries, optional parameters, sorting, pagination, and temporal tables.

  • Real-world implementations have successfully searched datasets containing millions of records with sub-second performance.

  • sp_CRUDGen is available on GitHub along with supporting documentation, videos, and examples.

What Is sp_CRUDGen?

Developed by Emergent Software's Kevin Martin, sp_CRUDGen is a free open-source SQL Server stored procedure that automatically generates stored procedures based on your database tables and metadata.

The generated code leverages SQL Server community best practices and can dramatically reduce the amount of repetitive database development work required when building applications.

Rather than manually creating common CRUD operations and advanced search procedures, developers can use sp_CRUDGen to generate production-ready T-SQL code based on:

  • Table definitions

  • Foreign key relationships

  • Data types

  • Database metadata

One particularly powerful use case is generating highly dynamic search procedures that support complex filtering, sorting, and pagination scenarios.

In one real-world implementation, sp_CRUDGen was used to generate dynamic T-SQL search functionality for a large receivables management application.

The Data Source

For this project, a view named dbo.AccountLookup was created containing 102 columns sourced from 14 different database tables.

Each of those 102 columns was automatically converted into searchable stored procedure parameters, allowing users to build highly customized search criteria through the application's interface.

The underlying data volume was significant:

  • 102 searchable columns

  • 14 source tables

  • Nearly 17 million rows of data

The architecture was designed with scalability in mind and expected to support hundreds of millions of rows over time, assuming proper indexing strategies are maintained.

This type of scalability is one reason why many organizations continue to rely heavily on SQL Server for large-scale business applications.

Search Features

The generated stored procedure supports highly flexible search functionality.

Users can create as many or as few search conditions as needed to locate specific records.

The search interface supports common SQL filtering operations including:

  • Equals

  • Not Equals

  • Greater Than

  • Less Than

  • Like

  • Between

Because the solution utilizes SQL Server system-versioned temporal tables, the generated search procedure also supports temporal querying features such as:

  • AS OF

  • FROM

  • BETWEEN

  • CONTAINED IN

  • ALL

Additional functionality includes:

  • Multi-column sorting

  • Pagination

  • Scrollable result sets

  • Dynamic optional parameters

These capabilities allow application users to efficiently search large datasets without requiring dozens of custom-built search procedures.

The Generated T-SQL Code

The generated search stored procedure is substantial.

In this implementation, sp_CRUDGen generated approximately 9,281 lines of T-SQL code.

After applying several project-specific customizations, the final version contained roughly 9,221 lines.

Custom modifications included:

  • Removing auto-generated tags

  • Adding custom quick-search parameters

  • Adjusting default sort behavior

  • Optimizing returned column subsets

  • Modifying common table expressions (CTEs)

To support future regeneration, documentation was maintained outlining which custom changes should be reapplied whenever the stored procedure is regenerated.

This approach allows developers to continue benefiting from automation while maintaining project-specific enhancements.

How Does It Perform?

In short: very fast.

Performance testing was conducted against large datasets on both physical SQL Server infrastructure and Azure SQL Database environments.

On a physical SQL Server environment featuring:

  • 16 CPU cores

  • 32 GB RAM

  • NVMe storage

Search execution times consistently ranged between 661 and 708 milliseconds after cache warm-up.

The benchmark involved searching records associated with ten of the most common last names in the United States.

Results across environments included:

  • SQL Server 16 Core / 32 GB RAM / NVMe: 661–708 ms after 5 second warm-up

  • 2,000 Premium DTUs: 1 second after 5 second warm-up

  • 800 Standard DTUs: 1–2 seconds after 47 second warm-up

  • 400 Standard DTUs: 2–3 seconds after 1 minute 13 second warm-up

  • 300 Standard DTUs: 3–5 seconds after 1 minute 32 second warm-up

  • 200 Standard DTUs: 4–8 seconds after 1 minute 36 second warm-up

  • 100 Standard DTUs: 9–10 seconds after nearly 6 minutes warm-up

  • 50 Standard DTUs: 17–23 seconds after extended warm-up periods

As expected, performance varied based on available CPU, memory, storage, and Azure service tier selection.

Organizations evaluating Azure SQL performance should consider how workload characteristics align with chosen pricing tiers and resource allocations.

sp_CRUDGen can generate 11 different stored procedure types ranging from basic CRUD operations to highly advanced dynamic search procedures.

These search procedures are often referred to as:

  • Optional parameter queries

  • Kitchen sink queries

  • Swiss army knife queries

  • Catch-all queries

Additional resources:

How Emergent Software Can Help

We help organizations design, optimize, modernize, and support SQL Server environments ranging from transactional business systems to enterprise-scale data platforms. Our team provides database architecture, SQL Server managed services, performance tuning, cloud migration, and application modernization services using Microsoft technologies and Azure best practices. If this sounds familiar, we can help.

Final Thoughts

sp_CRUDGen demonstrates how automation can significantly accelerate database development while still producing highly capable and scalable SQL Server solutions.

By leveraging metadata and established SQL Server best practices, organizations can reduce repetitive development work while maintaining flexibility, performance, and maintainability.

If you're evaluating SQL Server modernization, database automation, or performance optimization initiatives, Emergent Software is here to help. Reach out — we'd love to learn more about your goals.

Frequently Asked Questions

What is sp_CRUDGen?

sp_CRUDGen is a free open-source SQL Server stored procedure generator developed by Kevin Martin of Emergent Software. It uses database metadata such as table structures, foreign keys, and data types to automatically generate stored procedures. The tool helps developers reduce repetitive coding and accelerate database development efforts. It supports both simple CRUD operations and highly advanced dynamic search procedures. Many organizations use it to improve productivity and maintain consistency across SQL Server projects.

What kinds of stored procedures can sp_CRUDGen generate?

sp_CRUDGen can generate a variety of stored procedures including Create, Read, Update, Delete, Upsert, and advanced search procedures. The advanced search functionality supports optional parameters, dynamic filtering, sorting, and pagination. These procedures are often referred to as kitchen sink or catch-all queries. The generated code follows SQL Server community best practices. This flexibility makes the tool useful across many application types.

How does sp_CRUDGen support dynamic searching?

sp_CRUDGen can automatically generate dynamic search procedures that allow users to filter data across many fields without requiring separate stored procedures for every search scenario. It supports common SQL operators such as equals, greater than, less than, like, and between. The generated procedures can also support temporal table queries and multi-column sorting. This approach simplifies application development while maintaining strong performance. It is particularly useful for large business applications with complex search requirements.

Can sp_CRUDGen handle large datasets?

Yes. Real-world implementations have successfully used sp_CRUDGen-generated procedures against datasets containing millions of records. Performance depends heavily on indexing strategy, hardware resources, and database architecture. Testing has shown sub-second execution times on appropriately provisioned SQL Server environments. Azure SQL Database deployments have also demonstrated strong performance at higher service tiers. Proper database tuning remains important for optimal scalability.

Is sp_CRUDGen free to use?

Yes. sp_CRUDGen is an open-source project available through GitHub. Developers can download, review, and use the tool without licensing costs. Supporting resources including documentation, blog articles, and video walkthroughs are also publicly available. This makes it accessible for both individual developers and enterprise teams. Organizations can customize the generated code as needed for their specific requirements.

Why would organizations use a tool like sp_CRUDGen?

Organizations use tools like sp_CRUDGen to reduce repetitive database development work, improve consistency, and accelerate project delivery. Automated code generation helps developers focus on solving business problems rather than writing boilerplate SQL code. It also helps standardize stored procedure patterns across projects. For organizations managing large SQL Server environments, automation can significantly improve productivity. The result is often faster development cycles and easier long-term maintenance.

Author

Emergent Software