When you write code or a query, you typically do so on a workstation on your desk or via a browser. If the code needs to make a connection to a database to retrieve and parse some data, it is important to decide where the parsing should take place. There are two places where the data parsing can happen. The first is on your workstation or on the server responding to the browser. Keep in mind that processing data requires compute power—that is, CPU and memory resource—as well as networking capacity to send and receive the requested dataset. The other location where the data can be parsed is on the database server itself. When you parse data on the database server, you use a stored procedure. A stored procedure is SQL query syntax executed on the database server itself; any of the previous examples can be used. This snippet is an example of how to run a stored procedure:
EXECUTE [dbo].[GetMeditationScenarios]
The GetMeditationScenarios stored procedure would contain the SQL syntax to return exactly what the name implies. This not only makes it much easier for others to consume but also provides consistency in what you get in return when triggering the stored procedure. In contrast, any developer who wanted to get that data would have to write their own query that might miss some data, or they might misunderstand the entire meaning of the data. That would result in bad results and therefore wrong conclusions and wrong decisions. Not only do stored procedures improve performance and reduce networking latency, but they also make sure the data is easily accessible and interpreted correctly.
User‐Defined Functions
Functions are small snippets of code that get executed when you reference to the function name. You have been exposed to numerous functions that can be identified by open and closed parentheses (). Between the parentheses you can send parameters that the code within the function will use to flow through the code path execution. The functions you have read about so far are system or built‐in functions. It is possible to create your own function called a user‐defined function (UDF). The process has a great deal to do with which operating system, language, platform, and programming language you choose. With T‐SQL you can use the following syntax:
CREATE FUNCTION ValuesByFrequency (@frequencyid)RETURNS TABLEASRETURN( SELECT TOP 10 sc.SCENARIO, e.ELECTRODE, f.FREQUENCY, r.[VALUE] FROM SCENARIO sc, [SESSION] s, ELECTRODE e, FREQUENCY f, READING r WHERE sc.SCENARIO_ID = s.SCENARIO_ID AND s.SESSION_ID = r.SESSION_IDAND e.ELECTRODE_ID = r.ELECTRODE_IDAND f.FREQUENCY_ID = r.FREQUENCY_IDAND f.FREQUENCY_ID = @frequencyid)
Instead of writing the query multiple times, you can retrieve the data by executing this statement:
SELECT * FROM ValuesByFrequency(1)
This will return all brainwaves values for all readings.
