Hi people

I have the following store procedure that computes the sales between specific months in a year.
I build the query dynamically and submit it via EXEC. I need the result from the EXEC in the routine to output it. I'm now doing this via a temp table but I'd like to know if there is a better way to do this( e.g. using variables)



CREATE PROCEDURE dbo.usp_totalcomprasperiodo
@tcClienteID CHAR(10),@tiAno Numeric(4,0),@tiMesIni Numeric(2,0)=1,@tiMesFim Numeric(2,0)=12,@litotal Numeric(10,2) OUTPUT
AS
BEGIN
DECLARE @iMes Numeric(2,0)
DECLARE @lcSelect varchar(8000)
DECLARE @lcMes char(2)
DECLARE @lcfrom varchar(100)

SET @lcselect='select '
SET @iMes=@tiMesINi
WHILE (@iMes<=@tiMesFim)
BEGIN
SET @lcMes=RTRIM(LTRIM(STR(@iMes)))
SET @lcselect=@lcselect+' case when datepart(yyyy,d'+@lcMes+') = '+RTRIM(LTRIM(STR(@tiano)))+' then '
SET @lcselect=@lcselect+' ISNULL(t' +@lcmes+ ',0)+ISNULL(td'+@lcmes+',0)+ISNULL(tcc'+@lcmes+',0) else 0 end +'
SET @iMes=@iMes+1
END

SET @lcselect=LTRIM(RTRIM(@lcselect))
SET @lcselect=SUBSTRING(@lcselect,1,LEN(@lcselect)-1)+' as total from '
/*SET @lcselect=SUBSTRING(@lcselect,1,LEN(@lcselect)-1)+' from '*/

IF @tiano=YEAR(GETDATE())
SET @lcfrom='creditocliente where ID = '+@tcClienteID+''
ELSE
SET @lcfrom='creditoclientehistorico where clienteID ='+@tcClienteID+''+' and ano='+cast(@tiano as char(4))

SET @lcselect=@lcselect+LTRIM(RTRIM(@lcfrom))
SET @lcselect='INSERT INTO #tempTot '+@lcselect

Create Table #tempTot(
total Numeric(10,2))

EXECUTE (@lcselect)
SET @litotal = (select total from #tempTot)

drop table #tempTot
END

TIA