Defining/Calling a SP with a Table Type parameter

Let’s take this table “MyTable” as example:

Let’s say that we want to have a SP that allows us to add several items to this table in a single call. Among the many ways that we have to achieve this result, one of the cleaner is to send the new items all together in a Table Type parameter. Here is how to do it.


1) Creating a new Table Type parameter under Programmability/Types/User-Defined Table Types:

CREATE TYPE [dbo].[MyTableItemType] AS TABLE(
        [Name] [nvarchar](50) NOT NULL,
        [Value] [nvarchar](50) NOT NULL
)
GO

2) Creating a new SP that accepts a parameter of type MyTableItemType and use it to add the items to the table:

CREATE PROCEDURE [dbo].[MyTableAddMultipleItems]
        @items MyTableItemType READONLY
AS
BEGIN
        insert into MyTable (Name, Value)
        select Name, Value from @items
END

3) Calling the new SP and passing the Table Type parameter to it:

const string conStr = @"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True";
using (var con = new SqlConnection(conStr))
{
    con.Open();
    using (var cmd = new SqlCommand("MyTableAddMultipleItems", con)
    {
        CommandType = CommandType.StoredProcedure
    })
    {
        //Creating the table parameter
        var itemsTable = new DataTable();
        itemsTable.Columns.Add("Name");
        itemsTable.Columns.Add("Value");
        itemsTable.Rows.Add(new object[] { "Name 1", "Value 1" });
        itemsTable.Rows.Add(new object[] { "Name 2", "Value 2" });
        itemsTable.Rows.Add(new object[] { "Name 3", "Value 3" });
        itemsTable.Rows.Add(new object[] { "Name 4", "Value 4" });
        itemsTable.Rows.Add(new object[] { "Name 5", "Value 5" });
 
        //Sending the table parameter
        cmd.Parameters.AddWithValue("items", itemsTable);
        cmd.ExecuteNonQuery();
    }
    con.Close();
}

Ranking Functions (SQL Partitions)

http://www.besttechtools.com/articles/article/sql-rank-functions

-- Test table
declare @ExamResults table(
        studentName varchar(50),
        subjectName varchar(20),
        marks int)

insert into @ExamResults values('Adam','Maths',70)
insert into @ExamResults values ('Adam','Science',80)
insert into @ExamResults values ('Adam','Social',60)

insert into @ExamResults values('Rak','Maths',60)
insert into @ExamResults values ('Rak','Science',50)
insert into @ExamResults values ('Rak','Social',70)

insert into @ExamResults values('Sam','Maths',90)
insert into @ExamResults values ('Sam','Science',90)
insert into @ExamResults values ('Sam','Social',80)

select studentName, subjectName, marks
from @ExamResults

-- Ranking Functions
select studentName, subjectName, marks,

-- Row "qualification" within a subset of record having its same studentName value, order by marks.
-- Rows with the same marks value have the same rank; in this case the next rank will "jump" a position
RANK() over(partition by studentName order by marks desc) [rank],

-- The same as RANK(), but in this case if rows with the same marks value are present there's no "jump"
DENSE_RANK() over(partition by studentName order by marks desc) denseRank,

-- Row group number over a specified number of groups. Useful to split the rows into set of groups
NTILE(2) over(partition by studentName order by marks desc) ntile2,
NTILE(3) over(partition by studentName order by marks desc) ntile3,

-- Row number. If rows with the same marks value are present they will have different row numbers
ROW_NUMBER() over(partition by studentName order by marks desc) rowNumber
from @ExamResults

-- Example: list of students with just the subjects in wich the have the highest marks

-- 1) Solution without using Ranking Functions
select E1.studentName, E2.subjectName, E1.marks
from (
        select studentName, MAX(marks) marks
        from @ExamResults
        group by studentName
) E1
inner join @ExamResults E2 on E2.studentName = E1.studentName and E2.marks = E1.marks
order by studentName

-- 2) Solution using Ranking Functions
select E.studentName, E.subjectName, E.marks
from (
        select studentName, subjectName, marks,
        RANK() over (partition by studentName order by marks desc) [rank]
        from @ExamResults
) E
where E.[rank] = 1

And here is the result: