SQL stored procedure is also why business intelligence runs off SQL.
The only way to speak with relational databases (data linked with intelligent, intuitive connections) is a Structured Query Language. SQL is the coding language used to access, manipulate, store, and retrieve this data.
Business intelligence tools use SQL in different ways, so they can convert this relational data into usable reports and visualizations.
One popular BI tool is Microsoft SSRS. Specifically, SSRS users are looking to upgrade to newer and better technology (more on this later). Many users create reports in SSRS leveraging stored procedures, so we get asked quite often, does DashboardFox support stored procedures?
What is a SQL Stored Procedure?
An SQL stored procedure is a savable type of prepared SQL code, which is stored in the server database for later use and reuse.
This prepared code is a logically grouped set of SQL statements and procedural command logic. One stored procedure can perform multiple, complex business processes.
The server creates and caches its query execution plan when the stored procedure is initially called. Subsequently, calling the stored procedure triggers the server to reuse this plan, executing all actions server-side.
SQL parameter values can be passed through stored procedures, allowing one procedure to handle different requests.
SQL servers come pre-built to handle SQL stored procedure requests.
There are multiple types of stored procedures:
- User-defined stored procedures are created by the database user, usually a developer or administrator. These are stored in the current SQL Server database but not the master database.
- System stored procedures are prebuilt in the SQL server for enhanced server administration.
- Extended stored procedures function outside the SQL Server environment through dynamic-link libraries.
- CLR stored procedures are stored, managed .NET objects set to run in the database.
- Temporary stored procedures are only stored for as long as the client-server connection is maintained.
- Remote stored procedures are stored in remote server databases, then accessed from external servers with appropriate permission.
The Benefits of SQL Stored Procedures
There are several benefits of shifting processing functions to server-side stored procedures:
- Secure Operations. Users can run multiple procedures without directly accessing the database. They can also be encrypted to prevent source code visibility.
- Maintenance. Since operations are kept in the data tier, only procedures need to be updated for database changes. The application tier level is kept separate and doesn’t need to be changed when database layouts, relationships, or processes are modified.
- Lower Network Traffic. Only a single batch of code, the procedure name, is sent through the network to trigger operations. The rest of the query execution code is kept encapsulated on the server and doesn’t add to network traffic.
- Performance. The procedure execution plan only has to be created once – when the procedure is first to run. After this, it’s stored in the buffer pool for reuse. This reduces overall processing time and improves performance. Procedures don’t need to be recompiled unless there are significant changes to the tables or data it references.
- Code Reuse. Stored procedures eliminate constant rewrites for repetitive database operations. Any user or application with the right permission level can run code-free database operations. This eliminates redundancies and inconsistent coding.
- Modifiable. Code encapsulated within a stored procedure can be modified without affecting the application. Since the application is separated from the database, it doesn’t need to be recorded, restarted, or redeployed.
- Modular and Efficient. You can do business logic programming modularly without having rules spread across various application files. All business logic queries are grouped in one location then shared across multiple applications.
As you can see, stored procedures make things much better for the client and business users. It extends functionality and productivity while lowering end-user technical demands.
Disadvantages of SQL Stored Procedures
Of course, there are also drawbacks to having code locked away. This comes mainly for the developers:
- Testing and Debugging. Tests generally don’t show what went wrong and where. It’s difficult to test logic encapsulated in a stored procedure, and data errors aren’t generated until runtime. Workaround mechanisms, like creating a separate mirrored testing database, need to be used.
- Versions and history. Version control is not a built-in feature. Users can build systems to compensate, but the stored procedure does not show its version, identify when changes are made, or show its history. This becomes more of a problem when business process logic grows more complex and involves multiple stored procedures.
- Portability. Stored procedures with more complex functions have issues when upgrading to new versions of the same database or when changing database types.
- Rule Location and Categorization. Business rules are spread across various stored procedures, which don’t group as easily as individual files.
- Limited code function. Code encapsulated into specific procedures is not as powerful as application code.
- Set-Based Processing. Stored procedures need a certain level of complexity to be worth maintaining.
- Costs. Corporate culture, structure, and security concerns may require a separate, specialized, dedicated developer to handle stored procedures.
How Can DashboardFox Help With Stored Procedures?
First and most importantly, DashboardFox fully supports using SQL Stored Procedures.
So, if you are looking to switch from a BI tool or environment that heavily uses stored procedures, you don’t have to lose that investment. And if you have a bunch of existing reports that use SQL queries, you could convert those to a stored procedure (or view) and quickly migrate to DashboardFox.
How Does It Work?
In DashboardFox, when you register a SQL server, you’re able to select if SQL stored procedures are enabled for that server. DashboardFox allows you to leverage our semantic layer approach to building reports, or you can use existing stored procedures.
Via our access level security, you can also determine what users can build and run reports from stored procedures and the semantic layer.
Plus, you’re able to generate a dataset from stored procedures and then use DashboardFox to convert that dataset into metrics, visualizations, and more, just like you can any live query that DashboardFox does from a normal database.
Add our stored procedure support to the rest of the benefits of DashboardFox, including our self-hosted model meant to protect your data to the fullest, and our affordable pricing (read: no need to subscribe to anything!), and you already have gold in your hands.
Let us convince you that we can help you with this one by contacting us or scheduling a live session for free to see what we can offer for you in terms of SQL stored procedures and more.