Progressive sum

I have been struggling with progressive sum after reading some other people code who is using cursor to achieve this.

Before I provide the code, let me explain what is progressive sum. Progressive sum is usually used by accounting where you need to add or minus the value base on row level rather than give one value of sum. Below would be the example

ID	insertDate	value	Progressive_sum
1	2011-03-23 12:39:58.317	25	25
2	2011-03-23 12:39:58.320	15	40
3	2011-03-23 12:39:58.320	-15	25

As you can see on the row 1, we insert a value 25 and the progressive sum is 24. Once we insert value 15, the system automatically added 25+15 = 40.

Below would be the sample code that I use to produce the result above

create table #tmpAccounting
(
  ID int identity,
  insertDate datetime default getdate(),
  value float
)
insert into #tmpAccounting (value) values (25)
insert into #tmpAccounting (value) values (15)
insert into #tmpAccounting (value) values (-15)

select *, (select sum(value) from #tmpAccounting where insertDate<=a.insertDate and ID<=a.ID) as Progressive_Sum  from #tmpAccounting a order by ID

drop table #tmpAccounting

How to certain keyword in all your store proc?

I always need this code to find my store proc for thing like

- Table related back to a particular store proc
- I need to find certain SCR (software chage request) in all the store proc

So how do I do it? The fast and dirty way would be

---
Use MyDatabase;

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Kenny%'
GO
----

Base on the example above, I'm trying to search the word "Kenny" in all my store proc and return the store proc name. Please do take note that you will not be able to find exactly as what you want in a glance. You will find problem like comment or table or variable which have the same name.