Count nested id in sql server 2005

Suppose

i have a table treeview(tablename)  and four fields

nodeid,parentid,client_id and nodetype(it may be file or folder) 

Nodeid	Parentid	Client_id	nodetype	Folder_Name
1	1	2	Folder	A
2	1	2	Folder	B
3	1	2	Folder	C
4	2	2	Folder	D
5	2	2	Folder	E
6	3	2	Folder	F
7	3	2	File	G
8	4	2	Folder	H
9	4	2	Folder	I
10	3	2	Folder	J
11	3	2	Folder	K
12	5	2	Folder	L
13	8	2	Folder	M
14	9	2	Folder	N
15	10	2	Folder	O

These folders are nested like:

A

     B

          D

                H

                       M

                I

                       N     

          E

                L

     C

           F

           G

           J

                O

           K                 

Means ‘B’ is inside ‘A’ and ‘D’ is inside ‘B’

Now what I want to do

I want to count the folder if user click on ‘B’  it count all the folder that inside the ‘B’ and nested  in ‘B’

Show count=7 Folders

And  for the ‘C’ it count

Count=5

the query for this is:

WITH EmpCTE(nodeid, parentid, nodetype, lvl)
AS
(

  -- Anchor Member (AM)
  SELECT nodeid, nodetype, parentid, 0
  FROM treeview
  WHERE nodeid = 1
  UNION ALL
    
  -- Recursive Member (RM)
  SELECT E.nodeid, E.parentid, E.nodetype, M.lvl+1
  FROM treeview AS E
    JOIN EmpCTE AS M
      ON E.parentid = M.nodeid where e.nodetype='Folder'
)
SELECT * FROM EmpCTE

Split function in sql server 2005

 CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))      
 returns @temptable TABLE (items varchar(8000))      
 as      
 begin      
     declare @idx int      
    declare @slice varchar(8000)      
     
    select @idx = 1      
         if len(@String)<1 or @String is null  return      
      
    while @idx!= 0      
     begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)     
         else      


             set @slice = @String      
        
        if(len(@slice)>0) 
           insert into @temptable(Items) values(@slice)      
 
        set @String = right(@String,len(@String) - @idx)      
         if len(@String) = 0 break      
    end  
 return      
 end


and run it like:

select  * from dbo.split('Kshama,Parashar,Pushkar',',')

How to convert bytes in kb,mb and gb by sql server2005

A function to convert bytes into kb,mb,gb

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Create FUNCTION [dbo].[udf_FormatBytes]
(
   @InputNumber   DECIMAL(38,7),
   @InputUOM      VARCHAR(5) = 'Bytes'
)
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
   -- Declare the return variable here
   DECLARE @Output VARCHAR(48)
   DECLARE @Prefix MONEY
   DECLARE @Suffix VARCHAR(6)
   DECLARE @Multiplier DECIMAL(38,2)
   DECLARE @Bytes  DECIMAL(38,2)

   SELECT @Multiplier =
      CASE @InputUOM
         WHEN 'Bytes'         THEN 1
         WHEN 'Byte'          THEN 1
         WHEN 'B'             THEN 1
         WHEN 'Kilobytes'     THEN 1024
         WHEN 'Kilobyte'      THEN 1024
         WHEN 'KB'            THEN 1024
         WHEN 'Megabytes'     THEN 1048576
         WHEN 'Megabyte'      THEN 1048576
         WHEN 'MB'            THEN 1048576
         WHEN 'Gigabytes'     THEN 1073741824
         WHEN 'Gigabyte'      THEN 1073741824
         WHEN 'GB'            THEN 1073741824
         WHEN 'Terabytes'     THEN 1099511627776
         WHEN 'Terabyte'      THEN 1099511627776
         WHEN 'TB'            THEN 1099511627776
         WHEN 'Petabytes'     THEN 1125899906842624
         WHEN 'Petabyte'      THEN 1125899906842624
         WHEN 'PB'            THEN 1125899906842624
         WHEN 'Exabytes'      THEN 1152921504606846976
         WHEN 'Exabyte'       THEN 1152921504606846976
         WHEN 'EB'            THEN 1152921504606846976
         WHEN 'Zettabytes'    THEN 1180591620717411303424
         WHEN 'Zettabyte'     THEN 1180591620717411303424
         WHEN 'ZB'            THEN 1180591620717411303424
         WHEN 'Yottabytes'    THEN 1208925819614629174706176
         WHEN 'Yottabyte'     THEN 1208925819614629174706176
         WHEN 'YB'            THEN 1208925819614629174706176
         WHEN 'Brontobytes'   THEN 1237940039285380274899124224
         WHEN 'Brontobyte'    THEN 1237940039285380274899124224
         WHEN 'BB'            THEN 1237940039285380274899124224
         WHEN 'Geopbytes'     THEN 1267650600228229401496703205376
         WHEN 'Geopbyte'      THEN 1267650600228229401496703205376
      END

   SELECT @Bytes = @InputNumber*@Multiplier

   SELECT @Prefix =
      CASE
         WHEN ABS(@Bytes) < 1024 THEN @Bytes --bytes
         WHEN ABS(@Bytes) < 1048576 THEN (@Bytes/1024) --kb
         WHEN ABS(@Bytes) < 1073741824 THEN (@Bytes/1048576) --mb
         WHEN ABS(@Bytes) < 1099511627776 THEN (@Bytes/1073741824) --gb
         WHEN ABS(@Bytes) < 1125899906842624 THEN (@Bytes/1099511627776) --tb
         WHEN ABS(@Bytes) < 1152921504606846976 THEN (@Bytes/1125899906842624) --pb
         WHEN ABS(@Bytes) < 1180591620717411303424 THEN (@Bytes/1152921504606846976) --eb
         WHEN ABS(@Bytes) < 1208925819614629174706176 THEN (@Bytes/1180591620717411303424) --zb
         WHEN ABS(@Bytes) < 1237940039285380274899124224 THEN (@Bytes/1208925819614629174706176) --yb
         WHEN ABS(@Bytes) < 1267650600228229401496703205376 THEN (@Bytes/1237940039285380274899124224) --bb
         ELSE (@Bytes/1267650600228229401496703205376) --geopbytes
      END,
          @Suffix =
     CASE
         WHEN ABS(@Bytes) < 1024 THEN ' Bytes'
         WHEN ABS(@Bytes) < 1048576 THEN ' KB'
         WHEN ABS(@Bytes) < 1073741824 THEN ' MB'
         WHEN ABS(@Bytes) < 1099511627776 THEN ' GB'
         WHEN ABS(@Bytes) < 1125899906842624 THEN ' TB'
         WHEN ABS(@Bytes) < 1152921504606846976 THEN ' PB'
         WHEN ABS(@Bytes) < 1180591620717411303424 THEN ' EB'       
         WHEN ABS(@Bytes) < 1208925819614629174706176 THEN ' ZB'       
         WHEN ABS(@Bytes) < 1237940039285380274899124224 THEN ' YB'       
         WHEN ABS(@Bytes) < 1267650600228229401496703205376 THEN ' BB'       
         ELSE ' Geopbytes'
      END

   -- Return the result of the function
   SELECT @Output = CAST(@Prefix AS VARCHAR(39)) + @Suffix
   RETURN @Output

END
use like
select  *  from dbo.udf_FormatBytes(123456,'Bytes')