SQL Server : Window Functions

  1. Row_Number
  2. Rank
  3. Dense_Rank
  4. Ntile
Advertisements

Dynamic sql with output parameter in SQL Server

The following query outputs the count of customers belong to code ’65’ to a parameter using a dynamic query.

declare @query nvarchar(1000), @j int, @parmDefinition nvarchar(500);
set @query = N’select @i = count(*) from customer where code = ”65” group by code’
print @query
set @parmDefinition = N’@i int OUTPUT’;
execute sp_executesql @query,@parmDefinition,@i = @j output
select @j

Get a running total (cumulative total) in SQL Server

This is available from SQL Server 2012.

–drop table #TestTable

create table #TestTable
(
Id int identity(1,1),
SaleType nvarchar(30),
SaleDate datetime,
SaleUnits int,
RunningSaleUnits int
)

insert #TestTable (SaleType,SaleDate,SaleUnits)
select N’Sale’,N’2013-10-20 11:49:11.787′,10
union
select N’Sale’,N’2013-10-21 11:49:11.787′,4
union
select N’Refund’,N’2013-10-22 11:49:11.787′,-5
union
select N’Sale’,N’2013-10-23 11:49:11.787′,6
union
select N’Sale’,N’2013-10-24 11:49:11.787′,5

select * from #TestTable order by SaleDate

update    t
set        RunningSaleUnits = Runningtotal
from    #TestTable t
join
(
select id,
sum(SaleUnits) over(order by SaleDate rows unbounded preceding) as Runningtotal
from #TestTable
) r
on t.Id = r.Id

select * from #TestTable order by SaleDate

User Defined Table (UDT) types and their usage in procedures

This is a simple example which shows how to create a data type and how it can be used to pass an array of data as an input parameter to a stored procedure.

create table Customer
(
CustomerId int identity(1,1) primary key,
CustomerCode nchar(3),
CustomerName nvarchar(50)
)
go

if type_id(‘udt_Customer’) is not null
drop type udt_Customer;

create type udt_Customer as table
(
CustomerCode nchar(3),
CustomerName nvarchar(50)
);
go

create procedure proc_CustomerInsert
(
@customer udt_Customer readonly
)
as
begin

insert Customer (CustomerCode,CustomerName)
select CustomerCode,CustomerName
from   @customer

select * from Customer

end
go

———
declare @customer udt_Customer

insert @customer(CustomerCode,CustomerName)
select ‘001’,’Ethan Hunt’
union
select ‘002’,’James Bond’

exec proc_CustomerInsert @customer
go

SQL Code Snippets : Search Triggers

List all the triggers belong to a table

select o.name,p.name
from sys.objects o
join sys.objects p on o.parent_object_id = p.object_id
where o.type = ‘TR’
and p.name = ‘table_name’

Find the table a trigger belongs to

select o.name,p.name
from sys.objects o
join sys.objects p on o.parent_object_id = p.object_id
where o.type = ‘TR’
and o.name = ‘trigger_name’

Search the contents of a trigger

select o.name,p.name
from sys.objects o
join sys.objects p on o.parent_object_id = p.object_id
where o.type = ‘TR’
and object_definition(o.object_id) like ‘%search string%’

SQL Code Snippets : Search Procedures and Functions

Search contents of a procedure
select * from sys.procedures where object_definition(object_id) like ‘%search string%’

Search procedures by name
select * from sys.procedures where name like ‘%search string%’

Search Table Value Functions
select * from sys.objects where object_definition(object_id) like ‘%search string%’ and type = ‘TF’

Search Scalar Functions
select * from sys.objects where object_definition(object_id) like ‘%search string%’ and type = ‘FN’

Search for columns in a table
select t.name as TableName, c.name as ColumnName
from sys.tables t
join sys.columns c on t.object_id = c.object_id
where c.name like ‘%column%’
and t.name like ‘%table%’