Sql server-Useful queries

select *,max(sal) over (partition by DepartmentCode ) FROM emp  Result:

EmployeeNumber	FirstName	LastName	DepartmentCode	sal	max_sal	(No column name)
268-046	Martine	Nyamoto	ACNT	100	NULL	100
382-505	Paula	Waters	HMRS	100	NULL	300
773-148	James	Larsen	RSDV	100	NULL	300
495-728	Robert	Simms	RSDV	200	NULL	300
958-057	Peter	Aut	HMRS	200	NULL	300
279-242	Ann	Welch	HMRS	200	NULL	300
572-384	Jeannette	Welch	HMRS	300	NULL	300
283-947	Timothy	White	RSDV	300	NULL	300

Useful Links

different where conditions with isnull in sql server

http://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic
http://www.sommarskog.se/dyn-search.html
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx

Where Condition Value or Null(If NULL return all rows)

Null

create table t1_null(sno int)
create table t2_null(sno int)
insert into t1_null values(1),(2),(3),(null),(null)
insert into t2_null values(1),(2),(3),(null),(null)

alter proc test(@ptsno int)
as
declare @vsno  int
begin
if(@ptsno='')
select @vsno=''
else
set @vsno=@ptsno
end
--select a.sno from t1_null a, t2_null b where case when a.sno is null then '' end ,a.sno
select sno from t1_null where (sno=@vsno or(@vsno is null or @vsno=''))
--(ID_Provider_Primary = @IDProvider OR (@IDProvider IS NULL OR @IDProvider = 0))
--http://p2p.wrox.com/sql-server-2000/35645-case-statement-where-clause-logic.html