Using Stored Procedures In Embedded Analytics | Reveal BI (2022)

By Bilyana Petrova 9 min read

Using stored procedures is an easy way tosave time from frequently writing the samelines ofcode and alsoexpedite the call and execution ofthedesired query.

But what do you know about stored procedures, how they work and what advantages they provide to users?

From this article, you’ll learn what a stored procedure is, what are its benefits,how does it differentiate itself in comparison to functions, how to create a stored procedure,and last, but definitely not least how to use stored procedures inembedded analytics.

What Is Stored Procedure?

A stored procedure is aset ofSQLstatementsand other PL/SQL constructsthatare stored in a relational database management system (RDBMS) as a groupthat you can save and reuse over and over again.A stored procedure canconsist ofmultipleSQLstatementslike SELECT, INSERT, UPDATE or DELETE.They run as a unit and are used to solve a specific problem or perform a set of related tasks.That means that if you have an SQL query that you need and write repeatedly, you can save it as a stored procedure and thencall it to perform the query.

Using Stored Procedures In Embedded Analytics | Reveal BI (1)

If you are using Microsoft SQL Server,you canfindstored proceduresbyexpandingthe Programmabilitynode under your database in the Object Explorer of SQL Server Management Studio.

Using Stored Procedures In Embedded Analytics | Reveal BI (2)

Benefits of Using Stored Procedures

Stored procedures are one of the most used and most popular database subjects and as such,they offera handful of benefits.Using stored procedures can simplify and acceleratethe execution ofSQLqueries.

Using stored procedures, for example, can reduce network traffic between servers and clients. This is a result of the commands being executed as a single batch of code – so, instead of every single line of code being sent for execution individually, only the call toexecutethe storedprocedureis sentto the server.

Here are some of theotherbenefits of using stored procedures:

Maintainability

Maintaining a stored procedure is quite easyon the server in contrast to maintaining copies onnumerousclient machines.

Also, scriptsof proceduresare stored in one location,but the stored procedures can be freely used in other applications too. Intheevent ofa definitionchange, that is not at all aproblem asonly the stored procedure will be affected and not the application that calls it.

Performance & Efficiency

Stored procedures are compiled just once and then can be used many times.This saves a lot oftimewriting the samemultiple times if you are doing ad-hocqueries andgives a slight performance edge on the first execution ofa stored procedure vs. the first execution of an ad-hoc query.

See Reveal in Action

Includes Complex Business Logic

While notthe best place for complex business logic, a stored procedure is basically a function call on a database.You can include IF statements, LOOPS, security checks, and much more toensure the correct data is being sent back to the call of the stored procedure.

While somewhat debatable based on yourteam’sskillsets, best practiceslean towardsputting business logic for an applicationon the application side, however, stored procedures and T-SQL are a very powerful syntax forlogic execution.

Easy to Modify

You can quickly change one or more statements in a storeprocedurewith the help of the ALTER PROCEDUREcommand, which makes it very fast and easy to update the results sent back to the calling app. Contrary to a middle-tier component that includes business logic,which would need a potentially costly re-deployment, a quick change to a stored proceduretakes seconds.

Consider this stored procedure, where the@OrdYearvalue is hard-coded.Adeveloper or database administrator can update the@OrdYearvariablewith easeusing the ALTER PROCEDURE command.

ALTER PROCEDURE [dbo].[SalesByCategory] @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' AS IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN SELECT @OrdYear = '1998' END SELECT ProductName, TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) FROM [Order Details] OD, Orders O, Products P, Categories C WHERE OD.OrderID = O.OrderID AND OD.ProductID = P.ProductID AND P.CategoryID = C.CategoryID AND C.CategoryName = @CategoryName AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear GROUP BY ProductName ORDER BY ProductName 

Security

Another great benefit of using stored procedures is the security it provides to its data source. You can enhance the security of an application using stored procedures by restricting unauthorized access. For example, youcan grant someone access to a procedure that updates the database table without allowing access to thetableitself.You can also use stored procedures to “hide” the underlying table names from the end-user of the stored procedure. Since the caller of the stored procedure only has the name of the stored procedure, not the name of the tables used in the stored procedure, they won’t be able to directly access the tables with your datain any malicious way.

Differences Between Stored Procedures and Functions

Stored procedures and functions are both database objects containing sets of SQL statementsused to complete tasks. And while they might seemlikethe same thingatfirst glance, both are quite different from each other.

These are some of the things that differentiate stored procedures and functions:

  • Functions can be called fromstoredprocedure whereasstoredprocedure cannot be called from a function.
  • A function does not allow output parameters whereas astoredprocedure allows both output and input parameters.
  • You cannot manage transactions inside a function, but you can do it inside astoredprocedure.
  • You can call a function using a selectstatement butcannot call astoredprocedure using a select statement.
  • A function has a return type and returns a value whereasstoredprocedure does not have a return type and returninga value is optional.
  • A function allows only a SELECT statement in it whereas astoredprocedure allows SELECT, INSERT, UPDATE, AND DELETE.
  • Stored procedure can be used to read and modify data whereas a function can only read it.
  • A function can be embedded in a SELECT statement whereas astoredprocedure cannot be utilized in a SELECT statement.
  • A function can’t return multiple result sets while astoredprocedure can.
  • We can use transaction is stored procedures, but they are not allowed in functions.

How to Create a Stored Procedure?

Creating a new stored procedure is straightforward. There are 3 basic steps:

1. In the SQL Server Object Explorer, Right-Click on the Stored Procedures node under Programmability.

Using Stored Procedures In Embedded Analytics | Reveal BI (3)

2. In the New Stored Procedure Template, modifythe CREATE PROCEDURE statement to include the tables, parameters, and query options to return the desired data from the stored procedure.

Using Stored Procedures In Embedded Analytics | Reveal BI (4)

3. Click the Execute button in the toolbar tocompile and save your stored procedure.

Using Stored Procedures In Embedded Analytics | Reveal BI (5)

There are multiple tools to create stored procedures, using the interactiveSQL Server Management Studio is the easiest, as it gives you an easy way to get startedconnecting to many different types of databaseservers, includingAzure and SQL Server Analysis Services.

How to Use Store Procedures in Embedded Analytics?

When using the Reveal Embed SDKthere are 2 ways to use stored procedures.

1. When you are creating a new visualization on a database that supports stored procedures, you’ll see a list of stored proceduresthat are available to you. You will treat the stored produced youselect just like any other data source in Reveal, the only difference being a parameterized stored procedure, which you can use in a Filter by passingvariables to the stored procedure.

Using Stored Procedures In Embedded Analytics | Reveal BI (6)

You can watch aquick 6-minute video on how to use stored procedures via the Reveal user interface here:

2. As Reveal is an SDK, you also have complete programmatic control over stored procedures and parameters via code. To pass a stored procedurename, along with any required parameters, use theChangeVisualizationDataSourceItemAsyncfunction. This example demonstratespassing a stored procedure namedCustOrderHistand passing theCustomerIDparameter.

public async override Task<RVDataSourceItem> ChangeVisualizationDataSourceItemAsync (string userId, string dashboardId, RVVisualization visualization, RVDataSourceItem dataSourceItem) { if (dashboardId == "StoredProcNorthWind") { var msSqlDsi = dataSourceItem as RVSqlServerDataSourceItem; if (msSqlDsi?.Procedure == "CustOrderHist") { msSqlDsi.ProcedureParameters["@CustomerID"] = "AROUT"; } else { msSqlDsi.Procedure = "MyProcedure"; msSqlDsi.ProcedureParameters["@SampleParam"] = "SampleParamValue"; } return dataSourceItem; } } 

Wrap up

Stored procedures are a powerful tool to query a database and return resultsto a calling app. You should consider using stored procedures to simplify the way you present data to Reveal SDK usersand to simplify how you deal with parameters in your application codevs. using error-prone and lengthy ad-hoc queries.

You can learn more about Reveal and how does it work by downloading our SDK or by scheduling a quick demo.

Harness the Power of Data

Grow your business with real-time, contextual data.

Request a Demo

Categories: Embedded Analytics

Top Articles

You might also like

Latest Posts

Article information

Author: Laurine Ryan

Last Updated: 12/22/2022

Views: 5864

Rating: 4.7 / 5 (77 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Laurine Ryan

Birthday: 1994-12-23

Address: Suite 751 871 Lissette Throughway, West Kittie, NH 41603

Phone: +2366831109631

Job: Sales Producer

Hobby: Creative writing, Motor sports, Do it yourself, Skateboarding, Coffee roasting, Calligraphy, Stand-up comedy

Introduction: My name is Laurine Ryan, I am a adorable, fair, graceful, spotless, gorgeous, homely, cooperative person who loves writing and wants to share my knowledge and understanding with you.