SQL Server, mySQL, OracleSQL, PostgreSSQL, IBM DB2, Terada, and other relational database systems rely on Structured Query Language (SQL) to manage and manipulate data.  We frequently utilize the CASE statement, along with its siblings WHEN, THEN, and ELSE, to perform conditional operations within SQL queries. In this blog, we will look at how to use these keywords in SQL Server and how versatile they are. Let's get this party started.

EXAMPLE OF A CASE STATEMENT
The SQL Server CASE statement is a useful tool for applying conditional logic within a query. It enables you to return different values based on the conditions you specify. A CASE statement has the following fundamental structure:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE elseResult
END

condition1, condition2, etc.: These are the conditions you want to evaluate.
result1, result2, etc.: The corresponding values or expressions to return if the conditions are met.
elseResult: The value to return if none of the conditions are met (optional).

In this article, we will look at several usage cases of the CASE statement in real-world business situations. For demonstration purposes, we will use the transactions table and retrieve all of the records using the basic SELECT * FROM transactions shown below.

We have the Number of Store column in the transactions table, and we want to use the CASE to return Yes for Number of Stores greater than or equal to 3 and No for less than 3 stores, and we want to give 5% to each customer who operates three or more stores, which will be calculated by the Sales Amount. In the third case, we want to be fair by offering a 5%, 8%, 10%, and 15% incentive to customers who operate one, two, three, or four locations, respectively. For each customer, the percentage bonus will be multiplied by the sales amount. For each customer, the percentage bonus will be multiplied by the sales amount. To deter consumers from owing us, we have a measure in place to penalize any client whose Previous Balance is larger than 0 and who does not have a credit facility arrangement with us. The CustomerID will be extracted from all columns in the Excel table. To accomplish this, the CASE began:

Run the query below.

SELECT
[Customer Name],
[Previous Balance],
[Credit Facility],
[Sales Amount],
[Number of Store],
CASE
WHEN [Number of Store] >= 3 THEN ‘Yes’
ELSE ‘No’
END AS [3 or more stores],
CASE
WHEN [Number of Store] >= 3 THEN [Sales Amount] * 0.10
ELSE 0
END AS Bonus,
CASE
WHEN [Number of Store] = 1 THEN [Sales Amount] * 0.05
WHEN [Number of Store] = 2 THEN [Sales Amount] * 0.08
WHEN [Number of Store] = 3 THEN [Sales Amount] * 0.010
WHEN [Number of Store] >= 4 THEN [Sales Amount] * 0.15
END AS [Fair Bonus],
CASE
WHEN [Previous Balance] >0 AND [Credit Facility]<>’Yes’ THEN [Previous Balance] * 0.10
ELSE 0
END AS Penalty
FROM
transactions

Click on Execute to run the code

From the screenshot below, for the first scenario, we have Yes for customer with 3 or more stores while No is assigned to customers with less than 3 stores. For the second scenario, we calculated the bonus value for customers operating three or more stores while customers with less than 3 stores received 0. With regards to the third scenario, we gave fair bonus to each of the customers based on the actual number of stores they operate. Finally, customers whose Previous Balance is greater than 0 and who do not have Credit Facility with us received 10% penalty.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Statement of the Problem
A business rule in Master Data Services is a rule that you employ to ensure the quality and accuracy of your master data.

Is it feasible to create a business rule that ensures a constant length of value within an attribute?
Unfortunately, there is no direct feature/condition/operator to accomplish this request; however, it can be handled by combining various features/conditions/operators.

Solution
Navigate to Administrative Tasks on the Master Data Service (MDS) site. Click on Manage >> Business Rules

Provide the Name and Description for the Business rule and Click on the Add section within Then

Select the Attribute ( one column ) and Operator as Must have a minimum length of
Post that provides the intended length value and Click Add. Finally, Click on Save.

Similarly, Add one with a Maximum length validation rule, which would finally result in the below aspect.

Click on Save to Create the Business Rule.
The Business Rule would be in Activation Pending State.

Output
The length of the value is 5.

Length of value is other than 5
a) Length < 5

b) Length > 5

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

A business rule in Master Data Services is a rule that you employ to ensure the quality and accuracy of your master data.
Is it feasible to create a business rule to verify that the value for a combination of columns (Composite key) is unique?

Solution
Navigate to Administrative Tasks on the Master Data Service (MDS) site.

Click on Manage >> Business Rules

Select the Model and Entity within which you need to add the Business Rule. And Click on Add.

Provide the Name and Description for the Business Key and Click on Add section within Then

Select the Attribute ( one column ) and Operator as : Must be Unique in Combination with the following attributes.

Post that select the other Columns which are part of Composite key (other columns that needs to be added as part of uniqueness) and Click Add.

Finally, Click on Save.
Click on Save to Create the Business Rule.

The Business Rule would be in Activation Pending State

To Activate it, Click on Publish All.

Post this, the Business Rule would in Active state:

Output
Composite Key is Unique

Composite Key is Duplicate

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Overview of Stored Procedures

• A stored procedure is a collection of one or more pre-compiled SQL statements or SQL code that you write in order to reuse the code.
• It is kept as an object on the database server. Each procedure has a name, a list of parameters, and T-SQL statements.
• Triggers, other procedures, and other applications such as Java,.Net, Python, and others can be used to invoke procedures.
• It supports nearly all relational database systems (SQL Server, Oracle, MYSQL, and so on).
• When a stored procedure is called for the first time, SQL Server creates an execution plan and stores it in cache memory.

How does the Stored procedure function?
The stored process has two types of parameters, which are as follows.

• Input parameters are values passed by the user to stored procedures.
• Output parameters are used to return the value of stored procedures.

It receives input parameters and returns numerous values to the calling process or batch in the form of output parameters.

How does the Stored procedure function?
The stored process has two types of parameters, which are as follows.

• Input parameters are values passed by the user to stored procedures.
• Output parameters are used to return the value of stored procedures.

It receives input parameters and returns numerous values to the calling process or batch in the form of output parameters.

The Benefits of Preserved Procedures

• The stored procedure decreases the amount of network communication between the application and the database server.
• Reusability decreases code inconsistency and prevents unnecessary code writes.
• Security- stored procedures are always secure since they give limited access to individuals on a need-to-know basis and control which processes and actions we can execute.
• SQL injection attacks are avoided.

Advantages and disadvantages of stored procedures

• Debugging is a little challenging.
• During testing, any data mistakes in handling errors are not generated until runtime.

Stored Procedure Types

• Stored Procedures Defined by the User
• System Stored Procedures (SSPs)

Let's make an Employee table and look at some instances of how to write user-defined stored procedures:

CREATE TABLE Employee (
EMPID INT PRIMARY KEY,
EMPName VARCHAR (50),
EMPAddress VARCHAR (Max),
EMPAge INT,
EMPSalary INT
)

--Inserting multiple records in one table.

INSERT INTO Employee (EMPID, EMPName, EMPAddress, EMPAge, EMPSalary)
VALUES (101, 'Peter', 'London', 28, 65000),
(102, 'Scott', 'Manchester', 32, 55000),
(103, 'Laura', 'Leeds', 42, 95000),
(104, 'Remy', 'Liverpool', 30, 35000)

Now check the table by running a select query.
SELECT * FROM Employee;

Now let’s create a Simple Stored Procedure.

Example 1. (Simple Stored Procedure for Select operation without any parameter).

CREATE OR ALTER PROC usp_EmployeeRecords
AS
BEGIN
    SELECT * FROM Employee
END

Stored procedure usp_EmployeeRecords was created, as you can see in the above screenshot. Now let’s execute the same procedure.
EXECUTE usp_EmployeeRecords;

After executing the procedure, we can see all the employee records are there in the results.

Example 2. (Stored Procedure with input parameter to insert record).

Now let’s create a stored procedure that accepts input parameters insert new records into the employee table, and shows all the records of the employee table.
CREATE OR ALTER PROC usp_InsertEmployeeRecords
@EMPID INT,
@EMPName VARCHAR (50),
@EMPAddress VARCHAR (Max),
@EMPAge INT,
@EMPSalary INT
AS
BEGIN
    INSERT INTO Employee VALUES (@EMPID, @EMPName, @EMPAddress, @EMPAge, @EMPSalary)
    SELECT * FROM Employee  --Checking if all the records inserted in Employee table or not.
END

As you can see in the above screenshot, the stored procedure usp_InsertEmployeeRecords is created. Now let’s execute the same procedure by passing parameter values.
EXECUTE usp_InsertEmployeeRecords 105, 'Maria', 'Leicester', 38, 70000

After executing the procedure, we can see a new record inserted in the employee table.

Example 3. (Stored Procedure with input parameter to update record).
Now let’s create a stored procedure that updates the records in the table.

CREATE OR ALTER PROC usp_UpdateEmployeeRecords
@EMPID INT,
@EMPAddress VARCHAR (Max),
@EMPSalary INT
AS
BEGIN
    UPDATE Employee
    SET EMPAddress = @EMPAddress, EMPSalary = @EMPSalary
    WHERE EMPID = @EMPID
    SELECT * FROM Employee WHERE EMPID = @EMPID    --Giving where condition along with select clause to retrieve only updated records
END

As you can see in the above screen shot, the Stored procedure usp_UpdateEmployeeRecords was created. Now let’s execute the same procedure by passing parameter values.
EXECUTE usp_UpdateEmployeeRecords 105, 'Leicester', 75000

After executing the procedure, we can see the record is updated for EMPID “105” in the employee table.

Example 4. (Stored Procedure with input parameter to delete a record).
Now let’s create a stored procedure that deletes records from the table by passing only one parameter.

CREATE OR ALTER PROC usp_DeleteEmployeeRecords
@EMPID INT
AS
BEGIN
    DELETE FROM Employee WHERE EMPID = @EMPID
    SELECT * FROM Employee
END

Stored procedure usp_DeleteEmployeeRecords was created, as you can see in the above screenshot. Now let’s execute the same procedure by passing parameter values.
EXECUTE usp_DeleteEmployeeRecords 105  -- we pass the EMPID here to delete particular record

Execute deleted records
After executing the procedure, we can see the record is deleted for EMPID “105” in the employee table.

Example 5. (Stored Procedure with input and output parameters).
In programming, we create a function to return the value the same way we create a stored procedure to return the value. Now let’s create a Stored procedure that returns the salary of the employee whose EMPName will be passed to that stored proc.

CREATE OR ALTER PROC usp_SearchEmployeeSalary
@EMPName VARCHAR (50),
@EMPSalary INT OUTPUT
AS
BEGIN
    SELECT @EMPSalary = EMPSalary FROM Employee WHERE EMPName = @EMPName
END

Stored procedure usp_SearchEmployeeSalary was created, as you can see in the above screenshot. Now let’s execute the same procedure that returns the salary of the employee whose EMPName will be passed to that stored proc.
DECLARE @Result INT
EXECUTE usp_SearchEmployeeSalary 'Laura', @Result OUTPUT
SELECT @Result AS EmployeeSalary

After executing the procedure, it retrieves the employee's salary whose EMPName passed to the procedure.

Example 6. (Stored Procedure with input and output parameters).
Let’s create a procedure to count how many employees are in the table whose salary is greater than or equal to 50,000.

CREATE OR ALTER PROC usp_TotalEmployeeCount
@TotalCount INT OUTPUT
AS
BEGIN
    SELECT @TotalCount = count(EMPID) FROM Employee WHERE EMPSalary >= 50000
END

After executing the procedure, it retrieves the employee's salary whose EMPName passed to the procedure.

Example 6. (Stored Procedure with input and output parameters).
Let’s create a procedure to count how many employees are in the table whose salary is greater than or equal to 50,000.
CREATE OR ALTER PROC usp_TotalEmployeeCount
@TotalCount INT OUTPUT
AS
BEGIN
    SELECT @TotalCount = count(EMPID) FROM Employee WHERE EMPSalary >= 50000
END

Stored procedure usp_TotalEmployeeCount was created, as you can see in the above screenshot. Now let’s execute the same procedure that counts how many employees are there in the table whose salary is greater than or equal to 50000.
DECLARE @Result INT
EXECUTE usp_TotalEmployeeCount @Result OUTPUT
Select @Result AS TotalEmpCount

After executing the procedure, it gives the count of how many employees are there in the table whose salary is greater than or equal to 50,000.

In this article, we have learned about the stored procedure, its advantages, disadvantages, types, etc. We have also gone through the examples of how to create stored procedures with and without parameters. Hope you liked it. Please share your feedback and suggestions in the comments section.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The Secure Hash Algorithm (SHA) is a group of cryptographic hash algorithms that generate a fixed-size hash value from input data. SQL Server includes operations for dealing with cryptographic hashes, such as the SHA algorithm. The SHA algorithms can be used in SQL Server in the following ways.

SHA-1
The HASHBYTES function in SQL Server allows you to compute a SHA-1 hash value for a specified input string. Here's an illustration.

DECLARE @InputString NVARCHAR(100) = 'Hello, world!';
DECLARE @HashValue VARBINARY(20);
SET @HashValue = HASHBYTES('SHA1', @InputString);
SELECT @HashValue AS SHA1Hash;

SHA-256
SQL Server also supports the SHA-256 hash algorithm using the HASHBYTES function. SHA-256 produces a 256-bit hash value.
DECLARE @InputString NVARCHAR(100) = 'Hello, world!';
DECLARE @HashValue VARBINARY(32);
SET @HashValue = HASHBYTES('SHA2_256', @InputString);
SELECT @HashValue AS SHA256Hash;

SHA-512
SQL Server also supports the SHA-512 hash algorithm, which generates a 512-bit hash value.
DECLARE @InputString NVARCHAR(100) = 'Hello, world!';
DECLARE @HashValue VARBINARY(64);
SET @HashValue = HASHBYTES('SHA2_512', @InputString);
SELECT @HashValue AS SHA512Hash;

Please note that the HASHBYTES function returns the hash value as a VARBINARY type. If you want to display the hash value as a string, you can use the CONVERT function to convert it to a hexadecimal representation.
SELECT CONVERT(NVARCHAR(MAX), @HashValue, 2) AS HexadecimalHash;

Keep in mind that SHA-1 is considered weak and insecure for many cryptographic purposes due to vulnerabilities discovered over time. SHA-256 and SHA-512 are currently more secure options.

When to use SHA hashes?
Storing passwords: Never store plain text passwords. Instead, store a SHA2 hash which cannot be reversed. When a user logs in, hash their entered password and compare it to the stored hash. Data integrity: Compute a SHA hash of a data set and store it. To verify data has not changed, recompute the hash and compare it to the stored value. Changed data will result in a mismatched hash.
Generate unique values: The unique SHA hash can be used to generate unique IDs, encryption keys, etc.
Verify file downloads: The host can provide a SHA hash of the file for the client to verify the downloaded file contents match the expected hash.

How to use SHA in SQL Server?
Use the HASHBYTES() function to generate a SHA hash in T-SQL code.
HASHBYTES('SHA2_512', 'SomeStringData')
The ALGORITHM parameter takes 'SHA1' 'SHA2_256' or 'SHA2_512'.
To hash passwords, use a salt value to protect against rainbow table attacks. Concatenate with a random unique salt before hashing.
DECLARE @Salt varchar(20) = 'RandomSaltString'

SELECT HASHBYTES('SHA2_512', 'MyPassword' + @Salt)

When checking hashes for data integrity, store the expected hash value alongside the data. Recompute the hash periodically and compare.

Benefits of Using SHA Hashing

• One-way hash allows securely storing sensitive data like passwords.
• A small change in input drastically changes the output hash, allowing the detection of altered/corrupted data.
• Hashing data into fixed-length output allows for easier comparisons and indexing.
• SHA is optimized for speed and designed to be cryptographically secure.

SQL Server's SHA hash functions are most useful for securely storing sensitive data, validating data integrity, and generating unique fingerprint values of data.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

To save JSON array data supplied as a parameter in a stored procedure and insert it into a table, you'll need to write stored procedures in a programming language supported by your database. I'll use SQL Server's T-SQL language as an example, but the concept may be applied to other database systems with minor changes.

Assume you have a table named MyTable with three columns: ID, Name, and Data. You wish to use a stored procedure to pass a JSON array as a parameter and insert each element of the JSON array into the MyTable table.

Here's an example of how to do this in SQL Server using a stored procedure.
-- Create a table to store the data
CREATE TABLE MyTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(255),
    Data NVARCHAR(MAX)
);

-- Create a stored procedure to insert JSON array data
CREATE PROCEDURE InsertJsonData
    @jsonData NVARCHAR(MAX)
AS
BEGIN
    -- Use OPENJSON to parse the JSON array
    INSERT INTO MyTable (Name, Data)
    SELECT 'ItemName', [value]  -- You can replace 'ItemName' with a specific name or retrieve it from JSON
    FROM OPENJSON(@jsonData)
END;

In this example
Create a table MyTable to store the data.
Create a stored procedure InsertJsonData that takes @jsonData as a parameter, which should be a JSON array.
Inside the stored procedure, we use the OPENJSON function to parse the JSON array and insert each element into the MyTable table.

You can call this stored procedure and pass your JSON array as a parameter like this.
DECLARE @json NVARCHAR(MAX);
SET @json = '[{"value": "Value1"}, {"value": "Value2"}, {"value": "Value3"}]';

EXEC InsertJsonData @jsonData = @json;

Replace the JSON array (@json) with your actual JSON data, and the stored procedure will insert each element into the MyTable table.

Keep in mind that the actual implementation may vary depending on your database system, but the general idea of parsing the JSON array and inserting its elements into a table should be similar across different database systems with JSON support.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.