title: Do Inner Join and execute Sp on Sql query
body: <p>i have query for search data like this :</p>
  
  <pre><code>select top 100 *  from Payment 
  where PayId is not null   and
  PayAccount like '%10158002%'and 
  PayCustName like '%XXXX XXXXX%'and 
  PayAmount like '%429000%'and 
  PayAmountPaid like '%429000%'and 
  PayResponse like '%00%'and
  PayRefNo like '%101306000013%'and
  cast(PayDatetime as date) = ( select convert(date , '03/06/2013', 103)) 
  order by PayDatetime DESC
  </code></pre>
  
  <p>and give me the result like this :</p>
  
  <pre><code>PayId   PayAccount  PayCustName PayAmount   PayAmountPaid   PayResponse PayRefNo    PayScreenText   PayReceiptText  PayDatetime PayBankCode
  49       10158002   XXXX XXXXX  429000        429000         00        101306000013             xxx              xxx   2013-06-03 11:43:18.210  112
  </code></pre>
  
  <p>and i have todo like this to generate the <code>PayResponse  00</code> text which is <code>success</code> by doing inner join with other table :</p>
  
  <pre><code>select para.Value2
  from Payment pay 
  inner join
  Parameter para
  on pay.PayResponse = para.Value1
  </code></pre>
  
  <p>and i should do check if the refno is used by another table or no by using another inner join but it used an stored procedure :</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[Rintis_StatusReversal]
  @RefNoPay as varchar (20),
  @Count as int output,
  @Response as int output,
  @Text as varchar(20) output
  
  
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
  
          Select  @Count = COUNT(*)
          from
              payment Pay
                  inner join
              Reversal Rev
                  on Pay.PayRefNo = Rev.PayRefNo        
          where Pay.PayRefNo = @RefNoPay
  
          if @Count &gt; 0
          Begin
          SET @Response = 1
          SET @Text = 'Reversed'
          End
          Else
          Begin
          SET @Response = 0
          SET @Text = 'Paid'
          End
  
  select @Response , @Text
  END
  </code></pre>
  
  <p>by execute the procedure i want to get the @text value which is <code>success</code> or <code>reversed</code>.
   and i want input the value to first query result to.</p>
  
  <p>so the query that i wanna get is like this :</p>
  
  <pre><code>  PayId PayAccount  PayCustName PayAmount   PayAmountPaid   PayResponse PayRefNo    PayScreenText   PayReceiptText  PayDatetime PayBankCode RefStatus
      49       10158002   XXXX XXXXX  429000        429000         00        101306000013             xxx              xxx   2013-06-03 11:43:18.210  112 Success
  </code></pre>
  
  <p>is it possible to do that? i still confused with complex sql query. thks for your help</p>