# SQL SERVER – Stored Procedures to Check for Pythagorean Triples

My last blog post about helping my daughter with math concepts (Solving Quadratic Equations with T-SQL) got a great response, so I wanted to share another useful programming technique for learning math – using stored procedures to check for Pythagorean triples.

The Pythagorean theorem states that for a right triangle, the square of the hypotenuse (the side opposite the right angle) is equal to the sum of the squares of the other two sides. This relationship generates Pythagorean triples – sets of three positive integers that fit the theorem. Some examples are (3, 4, 5) and (5, 12, 13). Pythagoras was an ancient Ionian Greek mathematician and philosopher who lived in the 6th century BC. He is best known for the Pythagorean theorem that bears his name, but he made many other important contributions to mathematics, philosophy, music theory and other fields.

I created a stored procedure in SQL Server to check if three input numbers represent a Pythagorean triple. Here is the code:

```CREATE OR ALTER PROCEDURE CheckPythagoreanTriple
@a INT,
@b INT,
@c INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @max INT, @other1 INT, @other2 INT;

-- Identify the maximum number and the other two
IF @a >= @b AND @a >= @c
SELECT @max = @a, @other1 = @b, @other2 = @c;
ELSE IF @b >= @a AND @b >= @c
SELECT @max = @b, @other1 = @a, @other2 = @c;
ELSE
SELECT @max = @c, @other1 = @a, @other2 = @b;

-- Check if it’s a Pythagorean triple
IF @max * @max = @other1 * @other1 + @other2 * @other2
SELECT 1 AS isPythagorean;
ELSE
SELECT 0 AS isPythagorean;
END
GO```

The procedure takes three integer inputs (@a, @b, @c) and an output parameter @isPythagorean. It squares each input, checks if the squares satisfy the Pythagorean theorem, and sets the output parameter to 1 if yes or 0 if no. Also, you do not have to follow any order to give inputs.

Some example calls:

```DECLARE @isPythagorean BIT;

EXEC CheckPythagoreanTriple @a = 3, @b = 4, @c = 5  -- 1

EXEC CheckPythagoreanTriple @a = 5, @b = 12, @c = 13 -- 1

EXEC CheckPythagoreanTriple @a = 7, @b = 24, @c = 25 -- 1```

This shows how stored procedures can implement reusable logic and simply encapsulate mathematical concepts. By abstracting the Pythagorean check into a procedure, I can teach my daughter about calling functions, passing inputs and outputs, and modular programming. Math class just got a little more fun!

Let me know in the comments if you find this helpful for teaching math or programming concepts. I’m happy to share more programming techniques that lend themselves to learning math.

Reference: Pinal Dave (https://blog.sqlauthority.com)

## SQL SERVER – T-SQL Fortune Cookies

• Jonathan Roberts
August 4, 2023 5:27 am

There is some redundancy in your SP. For example
(@aSquare + @bSquare = @cSquare) is exactly the same as (@bSquare + @aSquare = @cSquare) using the commutative rule of addition

CREATE OR ALTER PROCEDURE CheckPythagoreanTriple
@a INT,
@b INT,
@c INT,
@isPythagorean BIT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @max INT, @other1 INT, @other2 INT;

— Identify the maximum number and the other two
IF @a >= @b AND @a >= @c
SELECT @max = @a, @other1 = @b, @other2 = @c;
ELSE IF @b >= @a AND @b >= @c
SELECT @max = @b, @other1 = @a, @other2 = @c;
ELSE
SELECT @max = @c, @other1 = @a, @other2 = @b;

— Check if it’s a Pythagorean triple
IF @max * @max = @other1 * @other1 + @other2 * @other2
SET @isPythagorean = 1;
ELSE
SET @isPythagorean = 0;
END
GO

EXEC CheckPythagoreanTriple @a = 7, @b = 24, @c = 25, @isPythagorean = @isPythagorean OUTPUT;
Returns 1 not 0 as stated as it is a Pythagorean Triple

• As a thinking exercise, I thought I would see if I could see if this could be done with a set based approach. Below is my result. It produces an ugly execution plan and has an implicit conversion from int to bit. Regardless, It was a fun exercise. Thanks for the idea!

CREATE OR ALTER PROCEDURE CheckPythagoreanTriple
@a INT,
@b INT,
@c INT,
@isPythagorean BIT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

WITH c AS (
SELECT x
, MAX(x) OVER() – MIN(x) OVER() AS d
FROM (
VALUES (@a*@a)
, (@b*@b)
, (@c*@c)
) p(x)
)
SELECT @isPythagorean = COUNT(*)
FROM c
WHERE d = x;
END

• Carter Cordingley
August 8, 2023 8:30 am

CREATE OR ALTER PROCEDURE CheckPythagoreanTriple
@a INT,
@b INT,
@c INT
AS
BEGIN
SELECT TOP 1
CASE
WHEN pvalue + LEAD(pvalue) OVER(ORDER BY pvalue) = LEAD(pvalue, 2) OVER(ORDER BY pvalue) THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END IsPythagorean
FROM (
VALUES
(POWER(@a, 2)),
(POWER(@b, 2)),
(POWER(@c, 2))
) x (pvalue)
ORDER BY pvalue
END