Why are you using Dynamic SQL?

select 
 CredID
 , State
 , Address
from 
 Credit..CreditInfo 
where 
 CredID in (
  select CredID 
  from 
   CartDetail 
   inner Join CartContent 
    on CartDetail.CartContentID = CartContent.CartContentID 
  WHERE
   and CartContent.CartID = @tnCartID
   and CartContent.CreditCategory = @Category

>Also how can I reference result of select within procedure, e.g.
>select ...

This is harder. The first question is: do you really need to? Could you use derive tables or views to remove the need for creates a bunch of result sets that later get combined. If not, you have your choice of temporary tables or table variables with table variables being the perferred choice, as long as your running at least SQL Server 2000.

-Mike