...a quick example for you...

-- create some dummy tables for testing...

create table #tbl1 (id int)

create table #tbl2 (id int)

create table #tbl3 (id int)

create table #tbl4 (table_name varchar(20))

insert into #tbl4 select...

try this...it should give you want you want. I'm sure it can be improved upon, but it's a starting point.

--drop table #tableA

CREATE table #tableA

(

 ItemNumber int identity not null,

 BreakQty1 int,BreakQty2...

Try this...

create Table #sessions

(

sessionNumber int Identity (1,1) Not Null,

roomNumber varchar (10) Not Null,

dateofStart datetime Not Null,

dateofEnd datetime Not Null,

)

create Table #Rooms

(

roomID int Identity (1,1) Not Null,

roomnumber varchar(10),

rate smallmoney,

)

--roomNumber...

This line...

Convert(varchar(25),(CAST (@PREV_MONTH AS VARCHAR(11)) + '-'+ CAST (@CURR_MONTH AS VARCHAR(11))))

...is this one causing the problem, but I'm wondering why you would want to group by static data anyway? @PREV_MONTH and...

Won't this work?

select objName=object_name(sc.id), Length=len(sc.text)

into #tmpX

from syscomments sc

where OBJECTPROPERTY(sc.id,'IsProcedure') = 1

select objName, Length=sum(length)

from #tmpX

group by objName 

order by Length desc

--drop table #tmpX