Read more about the article Table Valued Parameters in Stored Procedures
Microsoft SQL Server

Table Valued Parameters in Stored Procedures

In most cases stored procedures are developed so that it accepts a number of input parameters with a single value. But a common requirement is to be able to submit multi value parameters. That is, parameters that accept several values as opposed to a single value.

In SQL Server 2008, table values parameters in stored procedures was introduced.

(more…)

Continue ReadingTable Valued Parameters in Stored Procedures

Add and edit Windows environment variables

Windows environment variables are a great way to store server specific information that may be accessed from different systems on the server. Connection strings, folder paths and ip addresses are common examples of what may be suitable to store as environment variables on the server.

(more…)

Continue ReadingAdd and edit Windows environment variables

Bollinger Bands in T-SQL

Bollinger Bands® is a technical analysis tool trademarked by its inventor, John Bollinger. The basis for the bands is a simple moving average (SMA). Standard deviation lines are calculated above and under this moving average. Those lines are the Bollinger Bands.

The bands illustrate volatility, since they move apart when there are large movments (volatility) in a stock price, and they move together when there are small movements. The stock price movements within these bands are then used as indicators in trading.

This blog post will show how to calculate Bollinger Bands in T-SQL. This calculation will only work on SQL Server 2012 and later.

(more…)

Continue ReadingBollinger Bands in T-SQL

Relative Strength Index RSI in T-SQL

The relative strength index (RSI) is a calculation in TA (Technical Analysis) which measures the strength in the direction of the momentum of a stock. It compares losses to gains in closing prices under a decided time period. The measure varies between 0 and 100. 100 means there are only gains in closing prices, and 0 means there are only losses.

In theory, a stock that is rises above a certain RSI threshold, normally 70, can be considered overbought and may be a selling candidate. That is because a reversal can be expected sooner or later when a stock is overbought. Conversely, a stock that sinks below 30 in RSI can be considered oversold and may be a candidate for buying.

This blog post will show how to calculate Relative Strength Index RSI in T-SQL. It will use a time period of 14 periods. The calculation works on all versions of SQL Server.

(more…)

Continue ReadingRelative Strength Index RSI in T-SQL

MACD in T-SQL

The MACD (Moving Average Converenge Divergence) is a calculation in technical analysis used as a indicator of strength in a trend, or momentum in a stocks closing prices. The calculation uses different lengths of EMA (I covered the calculation of EMA in an earlier blog post here).

The MACD calculation uses the difference between a long EMA and a short EMA to create a oscillator (usually EMA12 – EMA26). The term Moving Average Converenge Divergence comes from the converging and divergence of these two moving averages. When the two moves towards each other there is convergence, and when they move away from each other, there is divergence. A divergence is commonly interpreted as a sign that the current trend is ending.

The MACD also moves around a zero line and when the MACD is above the zero line, that is used as a indicator of upward momentum (higher closing prices) since the short term EMA is above the long term EMA. And of course, if MACD is below the zero line this is an indicator of downward momentum.

Image by Kevin Ryde
Image by Kevin Ryde. Green line is MACD, red line is EMA9 of MACD.
White histogram is the difference between the two.

Further, a EMA9 is calculated for the MACD. This line is called the “Signal line”. The signal line is used in a trigger for buy and sell signals. More specifically, traders look for crossovers of the two lines. When the MACD moves over the MACD:EMA9, this is a buy signal since it indicates a upward momentum (a bullish market) of the closing prices. And when the MACD crosses under the MACD:EMA9, this is a sell signal since this is a indicator of downward momentum (bearish market).

This blog post will show how to calculate MACD in T-SQL. It works on all versions of SQL Server.

(more…)

Continue ReadingMACD in T-SQL

Exponential moving average in T-SQL

Exponential moving averages are similar to weighted moving averages in that they assign less weight to changes long ago, and more weight to recent changes. Weighted moving averages are linear, but exponential moving averages are exponential. That is, the weight can be expressed as a curve:

Weights in EMA
Weights in EMA

There is a great way to calculate exponential moving averages in T-SQL using an undocumented feature about variables and running totals in SQL Server. In this blog post I will show how to use that method to calculate exponential moving average in T-SQL, but I will also present a method that is using standard features in SQL Server. Unfortunately, that means using a loop.

(more…)

Continue ReadingExponential moving average in T-SQL

Line numbers in t-sql error messages

When you run scripts in SSMS and get an error, the error references a line number in the t-sql statement that caused the error. If you double click the error, SSMS will take you to the error. This works even if you have multiple statements in your query window. So it can be a really helpful feature. But if the error is in code that isn’t in the query window (for instance in a referenced stored procedure), nothing happens when you double click. In that case it can be good to know how SSMS calculates the line number in error messages.

(more…)

Continue ReadingLine numbers in t-sql error messages