title: How Temp Table works in Stored Procedures (global & local )
body: <p>Image #1: I am creating a stored procedure for inserting records into <code>#t1</code>.
  In the same session I am executing the Localtemp1 stored procedure for any number of times, and it works fine:</p>
  
  <p><a href="https://i.stack.imgur.com/TWvAt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TWvAt.png" alt="enter image description here"></a></p>
  
  <p>Image #2: again executing the stored procedure in another session &amp; works fine as well:</p>
  
  <p><a href="https://i.stack.imgur.com/Qr5hV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Qr5hV.png" alt="enter image description here"></a></p>
  
  <p>Image #3: now creating stored procedure for inserting records into <code>##tt</code>. For the first execution of globaltemp1 stored procedure, it works well:</p>
  
  <p><a href="https://i.stack.imgur.com/SPVk4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SPVk4.png" alt="enter image description here"></a></p>
  
  <p>Image #4: but when I executed it a second time, it is showing errors (does not exist in DB):</p>
  
  <p><a href="https://i.stack.imgur.com/FCSWp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FCSWp.png" alt="enter image description here"></a></p>
  
  <p>Image #5: then I closed the session where globaltemp stored procedure was created, and in a new session, I executed the stored procedure, and it works well for the first time:</p>
  
  <p><a href="https://i.stack.imgur.com/gl5yp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gl5yp.png" alt="enter image description here"></a></p>
  
  <p>Image #6: but when I execute it a second time, again it is showing errors (does not exist in DB):</p>
  
  <p><a href="https://i.stack.imgur.com/2w7j1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2w7j1.png" alt="enter image description here"></a></p>
  
  <p>What I know is scope of local temp &amp; global temp, but in stored procedures, they were completely different</p>
  
  <p>Can someone tell me </p>
  
  <ol>
  <li><p>Execution of localtemp1 stored procedure for many times gives output but while executing globaltemp1 sp for the first time gives output and second time results in an error </p></li>
  <li><p>As far as I know, after execution of stored procedure temptable gets dropped. Then why localtemp1 stored procedure is getting executed across all sessions and many number of times?</p></li>
  <li><p>Why globaltemp1 stored procedure is executing once and for second time showing an error?</p></li>
  <li><p>Final one, Globaltemp stored procedure shows output in another session for the first time only when created session was closed</p></li>
  </ol>
  
  <p>I mean    </p>
  
  <ul>
  <li>56 ----> globaltemp sp was created</li>
  <li>57 ----> to get o/p i need to close 56</li>
  <li>58 ----> to get o?p i need to close 57   (  WHY ??? )</li>
  </ul>
  
  <p>I am a beginner at SQL and please, someone make me understand because if I don't find logic &amp; correct reason I could not dive into another topic.</p>