title: Create, drop and insert a temp table in a user-defined function
body: <p>I am trying to create a function as per my requirements.  </p>
  
  <p>But, when am creating or drop <code>#tempTable</code>, it is giving an error as: </p>
  
  <blockquote>
    <p>invalid use of a side-effecting operator 'drop object' within a function</p>
  </blockquote>
  
  <p>My understanding is that we can't have <code>create</code>, <code>drop</code> or <code>insert</code> operations on <code>#temptable</code> in a function.</p>
  
  <p>Is that correct?  </p>
  
  <p>My SQL:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[RT_ResultFunction]
  (
      Id VARCHAR(4000)
  )
  RETURNS @RT_ResultFunction TABLE 
  (   
      Id VARCHAR(20)
      , Name varchar(20)
      ,Balance Int
  )
  AS
  BEGIN
      IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL  
         DROP TABLE #tempTable
  
      SELECT Id, COUNT(Balance) 
      INTO  #tempTable
      'Balance' FROM Table1
  
      INSERT  @RT_ResultFunction 
          SELECT T1.ID,T1,NAME,T2,Balance 
          FROM    Table2 T1, 
                  #tempTable T2
          WHERE T1.ID = T2.ID
  
      RETURN
  END
  </code></pre>