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.

Dynamic SQL refers to SQL statements that are constructed at runtime rather than being hardcoded into an application. It allows for more flexibility and dynamism in SQL queries. Here are some key points about Dynamic SQL:

• It allows you to create SQL statements dynamically based on input parameters or data values that are only known at runtime. The final SQL statement is not known until execution time.
• It allows you to dynamically build SELECT, INSERT, UPDATE, DELETE statements, etc. at runtime.
• It allows you to execute SQL statements directly from application code without having to hardcode the SQL.
• It provides flexibility as the SQL statement can change based on business logic and input parameters.

Here are some examples.

Example 1. Basic Dynamic Query
Suppose you have a simple database table named "Products" with columns "ProductID," "ProductName," and "Price." You want to create a dynamic SQL query to retrieve product information based on user-defined search criteria.
DECLARE @ProductName NVARCHAR(50) = 'Widget';
DECLARE @MinPrice DECIMAL(10, 2) = 10.00;
DECLARE @MaxPrice DECIMAL(10, 2) = 50.00;
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Products WHERE 1=1';

IF @ProductName IS NOT NULL
SET @SQL = @SQL + ' AND ProductName = @ProductName';

IF @MinPrice IS NOT NULL
SET @SQL = @SQL + ' AND Price >= @MinPrice';

IF @MaxPrice IS NOT NULL
SET @SQL = @SQL + ' AND Price <= @MaxPrice';

EXEC sp_executesql @SQL, N'@ProductName NVARCHAR(50), @MinPrice DECIMAL(10, 2), @MaxPrice DECIMAL(10, 2)',
                @ProductName, @MinPrice, @MaxPrice;

Example 2. Table Name as a Variable
Suppose you need to perform similar operations on different tables based on user input, and the table name itself is a variable.
DECLARE @TableName NVARCHAR(50) = 'Customers';
DECLARE @City NVARCHAR(50) = 'New York';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE City = @City';

EXEC sp_executesql @SQL, N'@City NVARCHAR(50)', @City;

Example 3. Using Dynamic Cursors
Dynamic SQL can also be used to generate and execute cursor-related statements based on certain conditions.
DECLARE @CursorName NVARCHAR(50) = 'ProductCursor';
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ProductID INT, @ProductName NVARCHAR(100);

SET @SQL = 'DECLARE ' + QUOTENAME(@CursorName) + ' CURSOR FOR SELECT ProductID, ProductName FROM Products';

EXEC sp_executesql @SQL;

OPEN @CursorName;

FETCH NEXT FROM @CursorName INTO @ProductID, @ProductName;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Process the data
PRINT 'ProductID: ' + CONVERT(NVARCHAR(10), @ProductID) + ', ProductName: ' + @ProductName;

FETCH NEXT FROM @CursorName INTO @ProductID, @ProductName;
END;

CLOSE @CursorName;

DEALLOCATE @CursorName;

Example 4. Basic Dynamic Query with Nested Subquery
Suppose you have two tables: "Orders" and "Customers," and you want to retrieve orders for a specific customer based on their name.
DECLARE @CustomerName NVARCHAR(100) = 'John Doe';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Customers
    WHERE CustomerName = @CustomerName
)';

EXEC sp_executesql @SQL, N'@CustomerName NVARCHAR(100)', @CustomerName;

Example 5. Dynamic Query with Nested Subquery and Conditional Logic
Consider an example where you want to retrieve orders for a specific customer and optionally filter by order status.
DECLARE @CustomerName NVARCHAR(100) = 'Jane Smith';
DECLARE @OrderStatus NVARCHAR(50) = 'Shipped';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Customers
    WHERE CustomerName = @CustomerName
)';

IF @OrderStatus IS NOT NULL
SET @SQL = @SQL + ' AND OrderStatus = @OrderStatus';

EXEC sp_executesql @SQL, N'@CustomerName NVARCHAR(100), @OrderStatus NVARCHAR(50)',
               @CustomerName, @OrderStatus;

Example 6. Dynamic SQL with Multiple Nested Subqueries
Let's say you want to retrieve a list of products along with their suppliers and categories, filtered by a specified category name.
DECLARE @CategoryName NVARCHAR(50) = 'Electronics';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
SELECT p.ProductID, p.ProductName, s.SupplierName, c.CategoryName
FROM Products p
INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE p.CategoryID IN (
    SELECT CategoryID
    FROM Categories
    WHERE CategoryName = @CategoryName
)';

EXEC sp_executesql @SQL, N'@CategoryName NVARCHAR(50)', @CategoryName;

Dynamic SQL provides the flexibility to adapt SQL queries to changing requirements or user inputs. Dynamic SQL with nested subqueries allows for the creation of complex and customizable queries based on runtime conditions. However, it also comes with some potential security risks, such as SQL injection if not handled carefully. Proper validation and sanitization of input parameters are crucial when using dynamic SQL to prevent these risks.

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.

Running totals are an important concept in SQL because they allow you to compute cumulative sums of values in your data. There are, however, two sorts of running totals to consider: unpartitioned and partitioned. In this post, we will look at these two types, their applications, and examples to help you understand their importance in data analysis.
Running Totals Without Partitions

Without any resets or specified criteria, unpartitioned running totals are generated throughout the full dataset.

They are useful when tracking cumulative values across all data sets, such as when computing cumulative sales over time.

Example
Assume we have the following data in a table called Orders.
--Create Table
CREATE TABLE Orders (
  Order_Date DATE,
  Customer_Id INT,
  Sales INT
);

-- Insert some data into the table
INSERT INTO Orders (Order_Date, Customer_Id, Sales) VALUES
('2023-01-01', 1, 100),
('2023-01-02', 1, 50),
('2023-01-03', 2, 200),
('2023-01-04', 2, 100),
('2023-01-05', 1, 100),
('2023-01-06', 2, 100);

To calculate an Unpartitioned running total of sales, you can use the following SQL query.
SELECT Customer_Id,Order_Date,Sales,
SUM(Sales) OVER (ORDER BY Order_Date) AS Running_Total
FROM Orders Order By Order_Date;

Output
The result accumulates the running total across all dates without any resets.

Partitioned Running Totals
Partitioned running totals are calculated over a subset of the data based on specific criteria or partitions.
They are useful when you want to calculate totals within specific categories or groups, like counting daily sales separately or calculating totals for different customer segments.

Example
Suppose we have the same table called Orders as above in the unpartitioned example.
To calculate a Partitioned running total of sales within each customer_id, you can use the following SQL query.
SELECT Customer_Id,Order_Date,Sales,
SUM(Sales) OVER (PARTITION BY Customer_Id ORDER BY Order_Date) AS Running_Total
FROM Orders Order By Customer_Id,Order_Date

The result accumulates the running total separately for each customer_id.

Summary
In this article, we learn about how to calculate running totals in SQL. Running totals are powerful tools for cumulative calculations. Understanding the difference between unpartitioned and partitioned running totals is essential for effective data analysis.

Unpartitioned running totals accumulate values across the entire dataset, while Partitioned running totals allow you to calculate totals within specific partitions, making them invaluable for segmenting and analyzing data. Incorporate these concepts into your SQL projects to enhance your data analysis capabilities.

If you find this article valuable, please consider liking it and sharing your thoughts in the comments.

Thank you, and happy coding!

In this article, I will go over the notion of the IIF Function in SQL Server. First, let's establish a database with some dummy data in it. I am supplying you with the database, as well as the tables holding the records, on which I am demonstrating the various examples. Let's see what happens.

CREATE DATABASE Peter_OFS
PRINT 'New Database ''Peter_OFS'' Created'
GO

USE [Peter_OFS]
GO

CREATE TABLE [dbo].[Employee] (
    EmployeeID INT IDENTITY (31100,1),
    EmployerID BIGINT NOT NULL DEFAULT 228866,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(255) NOT NULL UNIQUE,
    DepartmentID VARCHAR(100) NOT NULL,
    Age INT  NOT NULL,
    GrossSalary BIGINT NOT NULL,
    PerformanceBonus BIGINT,
    ContactNo VARCHAR(25),
    PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[tbl_Orders] (
    OrderId INT IDENTITY (108, 1) PRIMARY KEY,
    FoodieID INT,
    OrderStatus TINYINT NOT NULL, -- ==>> OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
    OrderDate DATE NOT NULL,
    ShippedDate DATE,
    RestaurantId INT NOT NULL,
);

Let's check our following tables by using the following queries.

1) To get the data from the "Employee" table, use the following query.
SELECT * FROM Peter_OFS..Employee

2) To get the data from the "tbl_Orders" table, use the following query.
SELECT * FROM Peter_OFS..tbl_Orders

The IIF Function
IIF is a logical function that returns one of two values based on whether the boolean expression is true or false. In other words, the IIF() method returns "true_value" if a condition is TRUE and "false_value" if it is FALSE.

Important PointsIn SQL Server, IIF is a logical function.

• SQL Server 2012 introduces IIF.
• IIF is an abbreviation for CASE Expression.
• IIFs can only be nested to a maximum of ten levels.
• The IIF function returns the data type with the highest precedence from the types "true value" and "false value."

Syntax
IIF(boolean_expression, true_value, false_value) is an IIF function.

• boolean_expression: A syntax error will be thrown if the argument is not a boolean expression.
• true_value: If boolean_expression evaluates to "TRUE", it returns the value supplied in the "true_value" parameter.
• false_value: If boolean_expression evaluates to "FALSE," the value specified in the "false_value" parameter is returned.

Examples
The examples in this section demonstrate the IIF Function's capability. Let's see what happens.
1) The IIF function compares integer values.
Because boolean_expression is true, the next example will return true_value.

SELECT IIF( 25 * 10 = 250, 'TRUE', 'FALSE' ) AS 'Result'

2) IIF Function with variables
In the following example, variables are used to calculate two integer values.
DECLARE @a INT = 25, @b INT = 12;
SELECT IIF( @a * @b = 300, 'TRUE', 'FALSE' ) AS 'Result'

3) IIF with String Functions
A) The following example accepts a string with a length greater than 10.
SELECT IIF(LEN('Hello! Scott') > 10, 'StringAccepted', 'StringRejected') AS [Result]

B) The following example checks the ASCII value.

SELECT IIF(ASCII('A') = 65, 'ASCIIAccepted', 'ASCIIRejected') AS [Result]

C) The following example compares string data using the IIF Function.
DECLARE @Person VARCHAR (25) = 'Peter'
SELECT @Person + ' likes ' + IIF(@Person = 'Peter', 'Mercedes-Benz Maybach', 'Audi A8') AS [Result]

4) IIF Function with data type precedence
SELECT IIF(21 < 11, 551.50, 551) Result

5) IIF Function with NULL
A) With NULL Constants
If we specify "NULL" in true_value and false_value, this statement will result in an error.
SELECT IIF( 25 * 12 = 300, NULL, NULL ) Result

B) With NULL Parameters
DECLARE @aa INT = NULL, @bb INT = NULL
SELECT IIF( 25 * 12 = 300, @aa, @bb ) Result

6) IIF Function With Aggregate Function
SUM()

The following example summarizes the total orders along with the order status.
SELECT
   SUM(IIF(OrderStatus = 1, 1, 0)) AS 'Completed',
   SUM(IIF(OrderStatus = 2, 1, 0)) AS 'Processing',
   SUM(IIF(OrderStatus = 3, 1, 0)) AS 'Pending',
   SUM(IIF(OrderStatus = 4, 1, 0)) AS 'Cancelled',
   COUNT(OrderId) AS 'Total Orders'
FROM tbl_Orders
WHERE YEAR(OrderDate) = 2021

7) Nested IIF Function (with GROUP BY Clause)

The following example summarizes the total orders along with the order status.
SELECT
   IIF(OrderStatus = 1, 'Completed',
      IIF(OrderStatus=2, 'Processing',
         IIF(OrderStatus=3, 'Pending',
            IIF(OrderStatus=4, 'Cancelled', '')
            )
         )
      ) AS [Order Status],
   COUNT(OrderId) AS 'Total Orders'
FROM tbl_Orders
GROUP BY OrderStatus

Points To Remember
In the key points, I have already mentioned that the IIF function is the shorthand form of the CASE Expression. And, yes, it's true. Internally, SQL Server converts IIF to CASE Expression and executes it.

Step 1
To check this, execute the following query with the "Actual Execution Plan" (Alternatively, press the "Ctrl + M" to include the Actual Execution Plan).
SELECT EmployeeID, CONCAT(FirstName , ' ' , LastName) AS [Full Name],
      Email, DepartmentID, GrossSalary,
      IIF(ContactNo IS NULL, 'Not Available', ContactNo) AS [Contact Number]
FROM Peter_OFS..Employee

Step 2
Now, right-click on "Compute Scalar" and click on the "Properties" option to proceed.

Step 3
And, you can see that SQL Server converts IIF to CASE expression internally.

Difference Between IIF Function and CASE Expression In SQL Server

Now, let's look at the quick difference between IIF Function and CASE Expression in SQL Server.

See you in the next article, until then take care and happy learning.

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.

In a SQL Server SELECT, INSERT, UPDATE, or DELETE query, you can refer to a temporary named result set known as a common table expression (CTE). CTEs, which were first made available in SQL Server 2005, are comparable to derived tables and views. Following a WITH clause, a CTE name and query expression are specified to define CTEs. The main query can refer to the CTE as if it were a table or view after the CTE query finishes and fills the CTE with data.

Some important benefits of CTEs

• CTE queries can be modularized into individual CTE blocks that can then be readily reused in different parts of the query. This increases readability and consistency.
• CTEs allow you to design recursive searches in which a CTE refers itself. This is beneficial for data that is hierarchical or tree-like.
• Simpler semantics: CTEs are easier to understand and write since they employ less sophisticated SQL syntax than derived tables.
• CTEs perform better than nested views and subqueries when it comes to optimization. The optimizer can use CTE results to improve performance in tempdb.

When Should CTEs Be Used?

• Here are some examples of frequent applications for CTEs:
• As previously stated, CTEs let you to split down complex logic into simpler modular parts, boosting readability.
• Reusable query logic - Once a CTE is defined, it can be referenced several times in the query. This reduces the need for repetitious logic.
• CTEs can recursively reference themselves to query hierarchical data such as org charts, folders, and so on.
• Replace views - In some circumstances, a CTE can accomplish the same thing as a view but with higher efficiency since the optimizer can better tune the CTE query.
• Replace derived tables - CTEs can be used to replace derived tables in order to simplify query syntax.
• Data exploration/investigation - Because CTE definitions are localized to a single statement, they might be beneficial for ad hoc data exploration prior to permanent table storage.

Now let's look at some examples to demonstrate how to write and use CTEs.

Syntax
WITH CTE_Name (Column1, Column2)

AS
(
    -- CTE Definition using SELECT
)

SELECT *
FROM CTE_Name

A WITH clause is used before the primary SELECT statement to define the CTE. Following the WITH keyword, you supply the CTE name and optional column list. The AS keyword denotes the beginning of the CTE definition query.

After you've defined the CTE, you may use it in the primary SELECT query just like any other table or view.

Consider the following example.

WITH Sales_CTE (SalesPerson, SalesAmount)

AS
(
   SELECT SalesPerson, SUM(SalesAmount)
   FROM Sales
   GROUP BY SalesPerson
)

SELECT *
FROM Sales_CTE

This CTE summarizes sales per person into a temporary result set named Sales_CTE. The main query simply selects from the CTE to display the sales summary.

Multiple CTEs
You can define multiple CTEs in a single query by listing them sequentially after the WITH clause:
WITH Sales_CTE (SalesPerson, SalesAmount)

AS
(
SELECT SalesPerson, SUM(SalesAmount)
FROM Sales
GROUP BY SalesPerson
),

TopSales_CTE (TopSalesPerson, TopSalesAmount)

AS
(
SELECT TOP 1 SalesPerson, SalesAmount
FROM Sales_CTE
ORDER BY SalesAmount DESC
)

SELECT *
FROM TopSales_CTE

Here we define two CTEs - Sales_CTE and TopSales_CTE. The second CTE references the first CTE. The main query selects the top salesperson from the second CTE.

Recursive CTE Example
One of the key benefits of CTEs is the ability to write recursive queries. Here is an example to find all managers and employees in a hierarchy.
WITH Managers_CTE (EmployeeID, ManagerID, EmployeeName, ManagerName, Level)

AS
(
-- Anchor member
SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, m.EmployeeName,
       0 AS Level

FROM Employees e
INNER JOIN Employees m
ON e.ManagerID = m.EmployeeID

UNION ALL

-- Recursive member that references CTE name
SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, m.EmployeeName,
       Level + 1
FROM Employees e
INNER JOIN Managers_CTE m
ON e.ManagerID = m.EmployeeID
)

-- Outer query

SELECT *
FROM Managers_CTE

The anchor member defines the root level of the hierarchy.
The recursive member joins back to the CTE name to get to the next level.
UNION ALL combines each round of recursion.
Outer query returns the final resultset.

This builds the org hierarchy iteratively until all levels are retrieved.
CTE with INSERT Example

In addition to SELECT, CTEs can be used with data modification statements like INSERT.
WITH Sales_CTE (SalesID, SalesPersonID, SalesAmount)

AS
(

SELECT SalesID, SalesPersonID, SalesAmount
FROM Sales
WHERE SalesDate = '20180901'
)

INSERT INTO SalesByDay (SalesDate, SalesPersonID, SalesAmount)
SELECT '20180901', SalesPersonID, SalesAmount

FROM Sales_CTE

This inserts sales for a specific date into a separate SalesByDay table using a CTE as the data source.

CTE with UPDATE Example
You can also leverage CTEs with updated statements.

WITH Sales_CTE (SalesID, SalesAmount)

AS
(

SELECT SalesID, SalesAmount
FROM Sales
WHERE SalesDate = '20180901'

)

UPDATE SalesByDay
SET SalesAmount = Sales_CTE.SalesAmount

FROM SalesByDay
INNER JOIN Sales_CTE
ON SalesByDay.SalesID = Sales_CTE.SalesID
WHERE SalesByDay.SalesDate = '20180901'

Here we populate matching rows in another table using values from the CTE.
CTE with DELETE Example

Similarly, CTEs can be utilized with DELETE statements.

WITH InactiveSales_CTE (SalesID, SalesDate)
AS
(
SELECT SalesID, SalesDate
FROM Sales
WHERE SalesDate < '20180101'
)

DELETE SalesByDay
FROM SalesByDay
WHERE SalesID IN (SELECT SalesID
             FROM InactiveSales_CTE)

This deletes related rows in another table based on inactive sales data from the CTE.

Temporary CTE Benefits
A key benefit of CTEs is that they are temporary named result sets that only exist during query execution. This provides several advantages.

• No need to persist CTEs in the database, unlike views or permanent tables. This reduces storage overhead.
• Can reference CTEs multiple times in a statement without repetitive subqueries or joins. Improves maintainability.
• Optimizer can tailor a temporary CTE query plan, unlike a persisted view which has a fixed query plan.
• Can replace inline derived tables and views to simplify and improve query semantics.
• Great for ad hoc data investigation before determining permanent tables.

In summary, CTEs are very useful in SQL Server for simplifying complex logic, improving query readability, handling recursive queries, and temporarily staging data transformations for business reporting and analysis. As you gain more experience with SQL Server, be sure to add CTEs to your development toolbox.

Features of CTEs

• Improved Readability and Maintainability: CTEs enhance the readability of complex queries by breaking them into smaller logical sections. This is especially useful when dealing with queries involving multiple joins, subqueries, or complex calculations. The segmented structure makes it easier to understand and troubleshoot the query.
• Modularity and Reusability: CTEs enable the creation of modular SQL code. You can define CTEs for specific tasks or calculations and then reuse them across different parts of the main query. This promotes code reusability, reduces redundancy, and simplifies the modification of specific parts of the query.
• Recursive Queries: CTEs are ideal for building recursive queries, where a query references itself to traverse hierarchical or recursive data structures. This is commonly used for tasks like navigating organizational charts, product categories, or tree-like data.
• Self-Joins and Window Functions Simplification: When dealing with self-joins or complex calculations involving window functions, CTEs provide a clearer and more organized way to express the logic. They break down intricate operations into manageable steps, leading to more concise and readable code.
• Code Organization and Reusability: Complex subqueries can be defined within CTEs, allowing for cleaner code organization. This organization makes it easier to understand the purpose of each part of the query, leading to improved maintainability.
• Optimization Opportunities: In some cases, SQL Server's query optimizer can optimize CTEs more effectively than equivalent subqueries. This optimization can lead to better execution plans and potentially improved performance.

Limitations of CTEs

• Single-Statement Scope: CTEs are scoped to a single SQL statement. They cannot be referenced across different statements in the same batch. This limitation can restrict their use in complex scenarios that involve multiple related statements.
• Performance Considerations: While CTEs enhance query organization, they may not always result in the most optimal execution plans. In certain cases, complex CTEs can lead to performance issues, especially when dealing with large datasets or intricate queries.
• Memory Usage: Recursive CTEs, which are used for hierarchical or recursive queries, can consume significant memory, particularly when dealing with deep hierarchies. This can lead to performance degradation if memory usage is not managed effectively.
• Lack of Indexing Support: CTEs do not support indexing. Unlike temporary tables, CTEs do not allow you to create indexes to improve query performance. This can be a limitation when working with large datasets that require efficient access patterns.
• Nested CTEs and Complexity: Nesting multiple CTEs within each other can lead to complex and challenging-to-maintain code. Overuse of nesting can make the query difficult to understand, debug, and optimize.
• Limited Use in Stored Procedures: CTEs are more commonly used in ad-hoc queries. While they can be used within stored procedures, their single-statement scope can sometimes be restrictive when working with multiple statements in a procedure.
• Complexity Management: While CTEs enhance query readability, they can also introduce complexity, especially when dealing with deeply nested or highly recursive queries. Overusing CTEs might lead to code that is harder to understand and maintain.

Conclusion
Common Table Expressions (CTEs) in SQL Server offer valuable features that enhance the readability, modularity, and organization of complex queries. Their ability to handle recursive operations and simplify self-joins and window functions makes them a versatile tool for developers. However, it's crucial to be aware of the limitations, such as single-statement scope, performance considerations, and lack of indexing support. By understanding both the features and limitations of CTEs, developers can leverage them effectively to create optimized and maintainable SQL code. Properly using CTEs requires a balance between leveraging their advantages and mitigating potential drawbacks.

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.

JSON (JavaScript Object Notation) has grown in popularity as a simple and versatile data format for delivering and storing information. SQL Server's JSON data type and many built-in functions allow you to store and handle JSON data. This tutorial will teach you how to query, index, and operate with JSON documents in SQL Server.

Create one table and work with a table named "members" to store member information, including locations in JSON format
CREATE TABLE Members (
    userID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
  Location NVARCHAR(max),
    Salary NVARCHAR(50)
);

What is the best way to insert JSON data into SQL Server?
JSON data can be stored in SQL Server using the JSON data type introduced in SQL Server 2016 and later editions. JSON documents can be directly stored in columns using the JSON data type, allowing for rapid querying and processing.

INSERT INTO Members (userID, FirstName, LastName, Location,Salary)
VALUES (2, 'Peter', 'Scott', '[{"Type": "Home", "Street": "London", "City": "lucknow", "Zip": "111111"}, {"Type": "Work", "Street": "s1", "City": "Manchester", "Zip": "111111"}]',70000);

How to query JSON data in SQL Server?
Using SQL Server to Query JSON Data: SQL Server has multiple techniques for interacting with JSON data. JSON_VALUE, which extracts a scalar value from a JSON string, is one of the most frequently used functions.
SELECT userID, FirstName, LastName,
    JSON_VALUE(Location.Value, '$.Street') AS Street,
    JSON_VALUE(Location.Value, '$.City') AS City,
    JSON_VALUE(Location.Value, '$.Zip') AS Zip,Salary
FROM Members
CROSS APPLY OPENJSON(Location) AS Location
WHERE JSON_VALUE(Location.Value, '$.Type') = 'Home';

How to filter data in JSON file?
The JSON_QUERY function allows you to filter JSON data based on set parameters.

SELECT userID, JSON_QUERY(Location) AS Membername
FROM Members;

How to update JSON data in SQL Server?
SQL Server has functions to add, update, and remove properties from JSON documents.

UPDATE Members
SET Location = JSON_MODIFY(Location, '$[2].street', 'New area')
WHERE userID = 2;

How to Aggregate JSON Data in SQL Server?
The FOR JSON clause can aggregate JSON data and return result sets using JSON formatting.
SELECT FirstName, LastName
FROM Members
FOR JSON auto ;

Thanks for reading, and I hope you like it.

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.

In the labyrinthine world of data modeling, using tools like SQL Server Analysis Services (SSAS) often feels like you're assembling a thousand-piece puzzle. The beauty and intricacy of a tabular model lie in its vast networks of tables, relationships, and measures, all working harmoniously to reveal valuable insights. However, without effective documentation, this puzzle might become a maze for those trying to understand it in the future.

I recently navigated the exciting process of working with a complex SSAS tabular model - an impressive structure of around 20 tables, a web of relationships, and an array of measures. Along the way, I realized the necessity for clear, thorough, and accessible documentation. Not only does it ease the navigation for future explorers of this model, but it also ensures the maintainability and scalability of the work.

Today, I'd like to share the comprehensive approach I used to document my tabular model, the best practices I discovered, and the strategies that made the process smooth and efficient.

Step 1. Harnessing the Power of Descriptions
Every object in SSAS, be it tables, columns, measures, or relationships, has a property known as "Description." I used this feature to provide a meaningful description for every object, allowing anyone reviewing the model to grasp its components' purpose and role quickly. Best practice tip. Always provide concise yet comprehensive descriptions, and maintain a consistent style.

Step 2. The Indispensable Data Dictionary
I created a data dictionary to detail table names, column names, their respective data types, descriptions, and any relevant notes. This serves as a reliable reference point, especially for those new to the model. Best practice tip. Keep the dictionary updated and synchronize it with the model to ensure they're always in alignment.

Step 3. Visualize with Diagrams
I used software like Visio to create comprehensive diagrams to represent the relationships between tables. These diagrams provide an overview of the model's interconnectedness, making it more comprehensible. Best practice tip. Make your diagrams clear and easy to follow, and ensure they represent the model's structure correctly.

Step 4. Detailing DAX Formulas
For each measure in the model, I meticulously documented the DAX formulas, explaining their purpose and logic. This transparency helps future developers understand the model's inner workings. Best practice tip. Be precise and detailed in your formula descriptions, explaining the "what" and the "why."

Step 5. Process and Refresh Strategy
I documented my process and refreshed my strategy to ensure reviewers understood how the cube's data stays up-to-date. This offers insights into when and how the data updates. Best practice tip. Include potential dependencies or bottlenecks in your documentation to provide a complete picture of the data refresh process.

Step 6. Centralizing the Information
With all the information at hand, it was crucial to present everything in a unified, accessible format. Tools like Microsoft Word and SharePoint allowed me to create a central hub of information that could be easily accessed and understood. Best practice tip. Keep your documentation easily accessible and organize it in an intuitive way.

Automation can be a game-changer. Third-party tools like Power BI Helper, DAX Studio, or SQL Power Doc for PowerShell can generate documentation automatically, saving you significant time and streamlining the process. Best practice tip. Regularly update and review auto-generated documentation to ensure it accurately reflects the current state of your model.

A crucial lesson from my experience. Effective documentation is a living entity; it grows, adapts, and evolves with your model. As a best practice, always keep it updated whenever you make changes to the model.

As we navigate the exciting world of data modeling, let's aim to make our data puzzles a little less puzzling and a lot more engaging!

Until next time, happy modeling!

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.

You must create a set of SQL Server stored procedures known as CRUD (Create, Read, Update, Delete) stored procedures in order to quickly and securely interact with your database tables. CRUD operations are crucial in database systems because they enable data modification.

How can we build a SQL server insert stored procedure?
You can use the Create operation to add new records to the database. creating a stored procedure.
CREATE PROCEDURE InsertProject
    @ProjectName NVARCHAR(500),
    @ClientName NVARCHAR(500),
    @ProjectManagerId NVARCHAR(500),
    @ProjectDescription NVARCHAR(500) = NULL,
    @StartDate DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Projects
    (
        [ProjectName],
        [ClientName],
        [ProjectManagerId],
        [CreatedDate],
        [ProjectDescription],
        [StartDate]
    )
    VALUES
    (
        @ProjectName,
        @ClientName,
        @ProjectManagerId,
        GETUTCDATE(),
        @ProjectDescription,
        @StartDate
    )
END

EXEC InsertProject
    @ProjectName = 'Management Software',
    @ClientName = 'Peter',
    @ProjectManagerId = 'A51DC085-073F-4D3A-AFC8-ACE61B89E8C8',
    @ProjectDescription = 'This is a sample Management Software.',
    @StartDate = '2023-01-20 12:45:00.000';

How may a Read stored procedure be created in the SQL server?
The Read operation is used to retrieve data from the database. You can create a select stored procedure by using a straightforward, constraint-free SELECT statement to retrieve all the data from a table.

CREATE PROCEDURE GetAllProject

AS
BEGIN
    SET NOCOUNT ON;
   SELECT Id, ProjectName, ClientName,ProjectDescription,StartDate,EndDate,CreatedDate from Projects
END

EXEC GetAllProject

Output

Use a WHERE clause with the proper condition to retrieve a specific row from the table using a unique identifier (such as Id) within a stored procedure.
CREATE PROCEDURE GetProjectByProjectId
     @Id INT
AS
BEGIN
    SET NOCOUNT ON;
   SELECT ProjectName,ClientName,ProjectDescription,CreatedDate from Projects
      WHERE Id = @Id;
END

EXEC GetProjectByProjectId @Id=2

Output

How we can create an Update stored procedure in the SQL server?

Existing records in the database can be modified using the Update operation.

CREATE PROCEDURE [UpdateProject]
     @id INT
    ,@ProjectName NVARCHAR(500)
    ,@ClientName NVARCHAR(500)
    ,@ProjectManagerId NVARCHAR(500)
    ,@ProjectDescription NVARCHAR(500)
    ,@StartDate DATETIME = NULL
    ,@EndDate DATETIME = NULL
    ,@UpdatedDate DATETIME = NULL
AS
BEGIN
    UPDATE Projects
    SET ProjectName = @ProjectName
        ,ClientName = @ClientName
        ,ProjectManagerId = @ProjectManagerId
        ,ProjectDescription = @ProjectDescription
        ,StartDate = @StartDate
        ,EndDate = @EndDate
        ,UpdatedDate = getutcdate()
    WHERE Id = @Id
END

SQL

EXEC UpdateProject
    @Id=1,
    @ProjectName = 'TimeSystem Software',
    @ClientName = 'Peter',
    @ProjectManagerId = 'A51DC085-073F-4D3A-AFC8-ACE61B89E8C8',
    @ProjectDescription = 'This is a sample TimeSystem Software.',
    @StartDate = '2023-01-20 12:45:00.000',
    @EndDate = '2023-04-20 12:45:00.000',
    @UpdatedDate= getutcdate();

Output

How we can create a Delete stored procedure in the SQL server?
To delete records from the database, use the Delete operation. To develop a stored process for deletion
CREATE PROCEDURE DeleteProjectById
   @id int
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Projects
    WHERE id = @id;
END

EXEC DeleteProjectById @Id=2

Output

To preserve accurate information and past information, gentle deletion rather than hard deletion is frequently used in database designs. Instead of physically removing records from the database, soft delete involves listing them as inactive or removed. This strategy enables previous tracking and data retrieval.
CREATE PROCEDURE [DeleteProject]
@id int
AS
BEGIN

UPDATE Projects SET IsDelete =1, IsActive =0
WHERE Id = @id

END

EXEC DeleteProject @Id=2

Output

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.