Database

 <br />
<br />
CREATE FUNCTION GetCount (@Request CHAR(1), @CatID VARCHAR(5)) <br />
RETURNS INT AS <br />
BEGIN <br />
<br />
Declare @Count INT<br />
<br />
IF @Request = 'W'  --Waiting for approval<br />
        SELECT @Count = count(RemitterRefNo) from TblRemittanceTxn<br />
       WHERE Category='O' AND  status=0 AND Catid=@CatID<br />
<br />
ELSE IF @Request = 'I' ---Incoming <br />
       SELECT @Count = count(RemitterRefNo) from TblRemittanceTxn<br />
       WHERE Category='I' AND  status=1 AND Catid=@CatID<br />
<br />
ELSE IF @Request = 'O' --OutGoing<br />
       SELECT @Count = count(RemitterRefNo) from TblRemittanceTxn<br />
       WHERE Category='O' AND  status=1 AND Catid=@CatID<br />
RETURN @Count<br />
<br />
END<br />

  <br />
Select distinct CatName, <br />
dbo.GetCount('W',catid) as waiting_count,<br />
dbo.GetCount('I',catid) as incoming_count,<br />
dbo.GetCount('O',catid) as outgoing_count<br />
from TblCategory <br />
<br />

create procedure dbo.usp_InsertZipCodeRange
@StartZip int,
@EndZip int
as begin
   declare @CurrentZip int

   if (@StartZip > @EndZip or @StartZip < 0) begin
      raiserror 'Invalid arguments specified'
      return @@ERROR
   end
   if exists (select 1 from dbo.ZipCodes
         where ZipCode between @StartZip and @EndZip) begin
      raiserror 'Specified range overlaps existing Zip codes'
      return @@ERROR
   end

   set @CurrentZip = @StartZip
   while (@CurrentZip <= @EndZip) begin
      insert into dbo.ZipCodes (ZipCode) values (@CurrentZip)
      set @CurrentZip = @CurrentZip + 1
   end

   return 0
end