How to combine multiple stored procedures into a single resulset

Let’s say, you have multiple stored procedures each of which is tied to a partial view or View Component in MVC. Now, while this may seem like a logical thing to do, but what if you want the entire payload to return to a single View.

Well, how about combine all the stored procedures and return a single result set. Here’s how in three simple steps.

First, let’s take a look at the diagram below:

The Employee table has the following records:

Following are the three stored procedures that I have created for the purposes of this demo:

CREATE OR ALTER PROCEDURE [dbo].[spGetMinimumSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN
	SET NOCOUNT ON;
	 
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary BETWEEN @MinSalaryRange AND @MaxSalaryRange)
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMedianSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN	
	SET NOCOUNT ON;
  
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
((Salary > @MinSalaryRange) AND (Salary <= @MaxSalaryRange))
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMaximumSalary]	
	@Salary MONEY	
AS
BEGIN
	SET NOCOUNT ON;
	   
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary > @Salary)
END

Now, comes the interesting part. Let’s create a temporary table:

CREATE TABLE #TmpSalary 
(
	FirstName NCHAR(25),
	LastName NCHAR(25),
	Gender NCHAR(10)
)

As you can see, the temporary table only has the structure, but no data:

Select * from #TmpSalary

Now, let’s insert the records into the temporary table that are returned from executing the stored procedures like so:

INSERT INTO #TmpSalary
EXEC spGetMinimumSalary 0, 50000

INSERT INTO #TmpSalary
EXEC spGetMedianSalary 50000, 75000

INSERT INTO #TmpSalary
EXEC spGetMaximumSalary 75000

Let’s view the records now:

Select * from #TmpSalary

There you have it! 🙂

Posted in SQL Server | Comments Off on How to combine multiple stored procedures into a single resulset

How to pass delimited list to a Stored Procedure

Let’s write a Table-valued function below. This can be created by navigating to Functions – Table-Valued Functions inside SQL Management Studio.

Just an FYI, I am using the AdventureWorks database, but this  sample can be used in any situation.

CREATE FUNCTION [dbo].[fn_SplitStateProvinceCode]
(
   @List VARCHAR(MAX),
   @Delimiter CHAR(1)
)
RETURNS TABLE 
AS 
  RETURN ( SELECT Item = CONVERT(varchar(500), Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );

For testing, you can declare a variable and test the statement by passing the @List and delimiter like so. As you can see, in the sample below, the delimiter is ‘,’.

Caution: The delimited string is strictly delimited by a comma and has no space in between province codes.

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
SELECT CODES = ITEM FROM dbo.[fn_SplitStateProvinceCode (@List, ',') 

If you wish to write it in a query using the Where clause and using ‘IN’ you can do the following:

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
Select * from Person.StateProvince 
WHERE StateProvinceCode IN (Select CODES = ITEM FROM dbo.fn_SplitStateProvinceCode (@List, ','))	

Cheers,

Obi

Posted in SQL Server | Comments Off on How to pass delimited list to a Stored Procedure

Global Query Filters in EF Core 2.0

Global Query Filters are a new addition to the arsenal of features in EF Core 2.0. This also implies that EF core team spearheaded by Diego Vega who are working diligently to bring EF Core at par with its Entity Framework 6.x classic counterpart. Like it or not, its good news for us developers, right?

Well, GQF in short are LINQ query predicates. In other words they are a boolean expression that are typically passed to the LINQ “Where” query operator and applied to Entity Types in the metadata model (usually in OnModelCreating). Such filters are automatically applied to any LINQ queries involving those Entity Types, including Entity Types referenced indirectly, such as through the use of Include or direct navigation property references.

Below is a Snapshot of the database containing two tables i.e. Department & Employee respectively.

GQFContext.cs class

namespace GlobalQueryFilters 
{
    class GQFContext : DbContext
    {
        public DbSet Department { get; set; }
        public DbSet Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"server=Obi-Oberoi; database=Company; Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity().HasQueryFilter(e =&gt; !e.IsDeleted);
        }

    }

Department & Employee POCO Classes

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace GlobalQueryFilters
{
    public class Department
    {
        [Key]
        public int DepartmentID { get; set; }
        public string Name { get; set; }
        public List Employee { get; set; }
    }

    public class Employee
    {
        [Key]
        public int ID { get; set; }
        [ForeignKey("DepartmentID")]
        public int DepartmentID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
        public decimal Salary { get; set; }
        public bool IsDeleted { get; set; }
        public Department Department { get; set; }
    }
}

Program.cs

 
static void Main(string[] args)
        {
            using (var ctx =  new GQFContext())
            {
                var emp = ctx.Employee.ToList();

                foreach (var item in emp.Where(e =&gt; e.DepartmentID == 3))
                {
                    Console.WriteLine("Name = {0},{1}", item.FirstName, item.LastName);
                }
                Console.ReadLine();
            }
        }

Notice the two records with the DepartmentID of 3 that are being filtered using the LINQ query above.

Now, let’s try and do something different. Let’s query the records with DepartmentID of 2. In the Employee table, we have at least two records with DepartmentID of 2. See below:

Notice the record for the employee Steve Pound has one of his records marked for deletion. That’s precisely what we intended for when using Global Query Filter which as you can recall from the code snippet below. This code is defined in the GQFContext class where we have specifically filtered the record(s) that are marked for soft deletion  like so:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity().HasQueryFilter(e =&gt; !e.IsDeleted);
        }

Notice, the record Steve Pound is filtered as a result of Global Query Filter that is defined as a predicate in the “HasQueryFilter” method.

Posted in ORM | Comments Off on Global Query Filters in EF Core 2.0

Difference between Varchar and Nvarchar. When to use what?

Character datatypes come in four varieties. They can be fixed-length or variable-length single-byte character strings (char and varchar) or fixed-length or variable-length Unicode characters strings (nchar and varchar).

Varchar stores ASCII values and Nvarchar stores Unicode characters.

Varchar can store up to 8000 characters, and Unicode character strings can store up to 4000 characters.

It is important to know the type of data you’ll be dealing with in order to decide between single-byte and double-byte character strings.

If you have requirements to store UNICODE or multilingual data, something that uses Chinese, Japanese or Korean character set, you are better off using nvarchar.

To put this in perspective, see the example below where VARCHAR and NVARCHAR character datatypes are being used.

In the first image, varchar uses single-byte storage whereas the second image nvarchar datatype uses double-byte character strings.

Summary:

Even though, varchar and nvarchar are more or less the same, the key difference between the two is that varchar stores ASCII characters whereas nvarchar stores Unicode characters. Unicode characters are typically associated with languages such as Japanese, Korean, Chinese etc.

Posted in SQL Server | Comments Off on Difference between Varchar and Nvarchar. When to use what?

EF Core 2.1 Roadmap

Ever since the release of Entity Framework Core 1.0 back in July 2016, its been a constant churning of releases packed with new features. There’s no doubt Microsoft will give all the love to this shiny new tool called EF Core, while still supporting the classic Entity Framework 6.x by adding small enhancements and making bug fixes.

That said, it’s exciting to note that we will be seeing some more features in the upcoming release of EF Core 2.1. Some of these potential features are the following:

  1. Lazy Loading (to allow the loading of virtual navigation properties on demand)
  2. Reverse Engineer View support
  3. Data Seeding (this feature is part of the fluent API that allows initial data to populate the database)
  4. Query Types (to allow the mapping of Views/Tables without primary keys)
  5. Allow for declaring Aggregates in the Model
  6. LINQ GroupBy Translation (this is being evaluated to SQL GROUP BY as opposed to In-Memory)
  7. Table Per Type mapping pattern
  8. Table Per Hierarchy inheritance mapping pattern
  9. Adding support for NoSQL database especially CosmosDB (My favorite)
  10. Oracle provider for EF Core (it’ll be a preview initially)
  11. Optimization of Correlated Subqueries (this will allow for the reduction of “N + 1” SQL queries to a bare minimum depending on the depth of the graph)
Posted in ORM | Comments Off on EF Core 2.1 Roadmap