Passing table to Function SQL Server

In SQL Server, there is no direct way of passing table to a function.

In this article, I am sharing how you can do it with the help of user-defined Type as table and Table-valued parameter which were introduced in SQL Server 2008

In SQL Server 2008, we can create user-defined Type as table that represents the definition of a table structure. We can then use this type to declare table-valued parameters for functions or to declare table variables that you want to use in a function.

Let's take an example where we will pass a table with one column and a separator to the function and it will return all table column values with the passed separator as a single value.

Step 1: Create a Type as Table with name TableType that will accept a table having one varchar column

create type TableType
as table ([value] [varchar](100) null)

Step 2: Create a function that will accept above declared TableType as Table-Valued Parameter and String Value as Separator

create function dbo.fn_get_string_with_delimeter (
    @table TableType readonly, @Separator varchar(5)
)
returns varchar(500)
as
begin
    declare @return varchar(500)
    set @return = stuff((select @Separator + value from @table for xml path('')),1,1,'')
    return @return
end

Step 3: Pass table with one varchar column to the user-defined type TableType and '-' as separator in the function

declare @tab TableType
insert into @tab
select 'Amit' union all
select 'Sandeep' union all
select 'Abhay' union all
select 'Ritesh'
select dbo.fn_get_string_with_delimeter(@tab, '-')

OUTPUT

Generate Weekdays in SQL Server

In this post, I am sharing simple script to generate weekdays in SQL server.

select  [DayId] = number
      , [DayName] = datename(dw, convert(datetime, cast(19000100 + number as varchar)))
from master.dbo.spt_values
where type = 'P' and number between 1 and 7

Result

Refer related posts

• generate list of dates in sql
• generate list of months in sql

Subtotal in SQL Query

Generate subtotals on different columns and grand total on a resultset in SQL is a very common requirement

In this article, I will show you how to achieve this easily with the help of Grouping function Rollup provided by Microsoft in SQL Server.

Example

Lets take Sales Table with fields year, month, prod_id, qty

Now, assume we have to find out the quantity of items sold product wise, month wise and year wise and total. Now let's write a query to calculate the same within the single query with the help of Grouping function Rollup.

DECLARE @sales TABLE(
    year INT, month INT, prod_id VARCHAR(10), qty INT
)

INSERT INTO @sales
SELECT 2011, 1, 'P1', 10 UNION ALL
SELECT 2011, 1, 'P1', 50 UNION ALL
SELECT 2011, 1, 'P2', 55 UNION ALL
SELECT 2011, 1, 'P2', 80 UNION ALL
SELECT 2011, 2, 'P1', 50 UNION ALL
SELECT 2011, 2, 'P1', 70 UNION ALL
SELECT 2011, 2, 'P2', 60 UNION ALL
SELECT 2011, 2, 'P2' ,20 UNION ALL
SELECT 2012, 1, 'P1', 50 UNION ALL
SELECT 2012, 1, 'P1', 25 UNION ALL
SELECT 2012, 1, 'P2', 15 UNION ALL
SELECT 2012, 1, 'P2', 20 UNION ALL
SELECT 2012, 2, 'P1', 50 UNION ALL
SELECT 2012, 2, 'P1', 70 UNION ALL
SELECT 2012, 2, 'P2', 60 UNION ALL
SELECT 2012, 2, 'P2', 20

SELECT [Year]   = COALESCE(CAST(year AS VARCHAR), 'Grand Total')
     , [Month]  = CASE WHEN month IS NULL AND year IS NOT NULL
                  THEN 'Total for Year:' + CAST(year AS VARCHAR)
                  ELSE CAST(month AS VARCHAR) END
     , [Product]= CASE WHEN prod_id IS NULL AND month IS NOT NULL
                  THEN 'Total for Month:' + CAST(month AS VARCHAR)
                  ELSE CAST(prod_id AS VARCHAR) END
     , [Qty]    = SUM(qty)       
FROM  @sales
GROUP BY year, month, prod_id
WITH ROLLUP

OUTPUT

In the above query, we have used COALESCE. Refer below post to understand COALESCE in detail

• COALESCE in SQL Server

Cumulative Sum in Sql Server 2012

In this article, I am sharing how cumulative total can be calculated very easily in SQL Server 2012.

Note: Solution will work in Sql Server 2012 onward.

For prior versions, refer related posts

• Cumulative Sum in SQL Server using Row_Number 
• Running Total in SQL Server using recursive CTE

Let's create Marks Table and populate some data in it

create table Marks(
     studid  VARCHAR(20),
     subcode VARCHAR(20),
     marks   INT
)
insert into Marks
select 'Stud1', 'English', 60 union all
select 'Stud1', 'History', 70 union all
select 'Stud1', 'Maths', 80 union all
select 'Stud1', 'Science', 75 union all
select 'Stud2', 'English', 55 union all
select 'Stud2', 'History', 60 union all
select 'Stud2', 'Maths', 57 union all
select 'Stud2', 'Science', 65

In SQL Server 2012, with the expansion of the OVER clause to include ORDER BY support with aggregates, it becomes very easy to calculate cumulative sum in SQL Server

Query to calculate cumulative sum for all subjects student wise

SELECT *, SUM(marks) OVER(PARTITION BY studid ORDER BY subcode) [Cumulative Sum]
FROM   Marks

OUTPUT