SQL Server Stored Procs - Revisited (Getting all children in SQL Server)

volvostephen

I was working on a stored procedure to return all children of a given folder. My procedure was working fine from Query Analyser but not fine when I tried a LiveReport. I thought I would do some investigation on what the problem was and this is what I found when stepping through builder....'Cannot insert the value NULL into column 'DataSize', table 'tempdb.dbo.#Tmp________________________________________________________________________________________________________________0001000024C2'; column does not allow nulls. INSERT fails.'I then modified my SQL to use IsNull and now it runs fine.The results from the below Stored Proc are all objects below a particular folder (Excluding projects and the whole negative data ID thing). It returns enough information that you can then do a query on the #tmp table for what you really want. You can get total folder size, # of objects, # of versions, folder depth etc. I hope someone finds this useful.