title: Stored Procedure "spins" when executed from Visual Studio 2012
body: <p>I have written 3 stored procedures and all run fine when executed in SQL Server.  However, when I execute them in Visual Studio 2012 they just &quot;spin&quot;, creating locks, etc.  Any ideas?</p>
  <p>Here is one of them.  Another one is selecting values from an inline view based on the same SQL and not inserting into #temp</p>
  <pre><code>    @StartDate      As DateTime,
      @EndDate        As DateTime,
      @EmployeeCode   As VarChar(50) = NULL 
  AS
  BEGIN
  
  SET NOCOUNT ON;
  
  
  select * into #temp
  from 
  (SELECT e.Code AS EMPLOYEE_ID,
     e.FirstName AS FIRST_NAME,
     e.LastName AS LAST_NAME,
     ec1.TransactionDate AS DATE_OF_SHIFT,
     ou.Code AS DEPT_UNIT_CODE,
     CASE
         WHEN ec1.Classification = '1' THEN
             MIN(ec1.TransactionDate)
     END AS CLOCK_IN,
     CASE
         WHEN ec2.Classification = '2' THEN
             MAX(ec2.TransactionDate)
     END AS CLOCK_OUT,
     pc.code,
     epb.hourvalue 
  FROM APIHealthcare_History_Live.dbo.Employee e (nolock)
  JOIN APIHealthcare_Live.dbo.EmployeeClocking ec1 (nolock)
      ON ec1.EmployeeID = e.ID
  JOIN APIHealthcare_Live.dbo.EmployeeClocking ec2 (nolock)
      ON ec1.OutClockingGuid = ec2.Guid
  JOIN APIHealthcare_Live.dbo.OrganizationUnit ou (nolock)
      ON ec1.OrganizationUnitID = ou.ID
  LEFT JOIN APIHealthcare_Live.dbo.EmployeePremiumBucket epb (nolock)
      ON ec1.Guid = epb.TransactionGuid
  JOIN APIHealthcare_Live.dbo.PayCode pc (nolock)
      ON epb.PayCodeID = pc.ID
  JOIN APIHealthcare_Live.dbo.PayGroupInstance pgi (nolock)
      ON pgi.ID = epb.PayGroupInstanceID
  WHERE 
  
  ec1.transactiondate between @StartDate and @EndDate
  and epb.PaymentClassification = '1'
  AND e.code = @EmployeeCode   
  
      AND e.ArchiveTaskID =
  (
      SELECT MAX(e2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
      WHERE e2.ID = e.ID
  )
  GROUP BY e.Code,
       e.FirstName,
       e.LastName,
       ec1.TransactionDate,
       ou.Code,
       ec1.Classification,
       ec2.Classification,
       pc.code,
       epb.hourvalue
  
  UNION
  SELECT e.Code AS EMPLOYEE_ID,
     e.FirstName AS FIRST_NAME,
     e.LastName AS LAST_NAME,
     ec1.TransactionDate AS DATE_OF_SHIFT,
     ou.Code AS DEPT_UNIT_CODE,
     CASE
         WHEN ec1.Classification = '1' THEN
             MIN(ec1.TransactionDate)
     END AS CLOCK_IN,
     CASE
         WHEN ec2.Classification = '2' THEN
             MAX(ec2.TransactionDate)
     END AS CLOCK_OUT,
     pc.code,
     epb.hourvalue 
  FROM APIHealthcare_History_Live.dbo.Employee e (nolock)
  JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec1 (nolock)
      ON ec1.EmployeeID = e.ID
  JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec2 (nolock)
      ON ec1.OutClockingGuid = ec2.Guid
  JOIN APIHealthcare_History_Live.dbo.OrganizationUnit ou (nolock)
      ON ec1.OrganizationUnitID = ou.ID
  LEFT JOIN APIHealthcare_History_Live.dbo.EmployeePremiumBucket epb (nolock)
      ON ec1.Guid = epb.TransactionGuid
  JOIN APIHealthcare_History_Live.dbo.PayCode pc (nolock)
      ON epb.PayCodeID = pc.ID
  JOIN APIHealthcare_History_Live.dbo.PayGroupInstance pgi (nolock)
      ON pgi.ID = epb.PayGroupInstanceID
  WHERE 
  ec1.transactiondate between @StartDate and @EndDate
  and epb.PaymentClassification = '1'
  AND e.code = @EmployeeCode     
  AND e.ArchiveTaskID =
  (
      SELECT MAX(e2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
      WHERE e2.ID = e.ID
  )
  AND ou.ArchiveTaskID =
  (
      SELECT MAX(ou2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.OrganizationUnit ou2 (NOLOCK)
      WHERE ou2.ID = ou.ID
  )
  AND pc.ArchiveTaskID =
  (
      SELECT MAX(pc2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.PayCode pc2 (NOLOCK)
      WHERE pc2.ID = pc.ID
  )
                               
  GROUP BY e.Code,
       e.FirstName,
       e.LastName,
       ec1.TransactionDate,
       ou.Code,
       ec1.Classification,
       ec2.Classification,
       pc.code,
       epb.hourvalue) as X
  
  select EMPLOYEE_ID, 
  FIRST_NAME, 
  LAST_NAME,
  DATE_OF_SHIFT, 
  DEPT_UNIT_CODE,
  CLOCK_IN, 
  CLOCK_OUT, 
  code, 
  sum(hourvalue) as Hours
  from #temp
  group by EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  DATE_OF_SHIFT,
  DEPT_UNIT_CODE,
  CLOCK_IN,
  CLOCK_OUT,
  CODE
  Order by CLOCK_OUT
  END
  </code></pre>
  <hr />
  <hr />
  <p>Here is the other one.</p>
  <pre><code>    @StartDate      As DateTime,
      @EndDate        As DateTime,
      @EmployeeCode   As VarChar(50) = NULL 
  AS
  BEGIN
  
  SET NOCOUNT ON;
  
  
  select EMPLOYEE_ID, 
  FIRST_NAME, 
  LAST_NAME,
  DATE_OF_SHIFT, 
  DEPT_UNIT_CODE,
  CLOCK_IN, 
  CLOCK_OUT, 
  code, 
  sum(hourvalue) as Hours
  from 
  (
  SELECT e.Code AS EMPLOYEE_ID,
     e.FirstName AS FIRST_NAME,
     e.LastName AS LAST_NAME,
     ec1.TransactionDate AS DATE_OF_SHIFT,
     ou.Code AS DEPT_UNIT_CODE,
     CASE
         WHEN ec1.Classification = '1' THEN
             MIN(ec1.TransactionDate)
     END AS CLOCK_IN,
     CASE
         WHEN ec2.Classification = '2' THEN
             MAX(ec2.TransactionDate)
     END AS CLOCK_OUT,
     pc.code,
     epb.hourvalue 
  FROM APIHealthcare_History_Live.dbo.Employee e
  JOIN APIHealthcare_Live.dbo.EmployeeClocking ec1
      ON ec1.EmployeeID = e.ID
  JOIN APIHealthcare_Live.dbo.EmployeeClocking ec2
      ON ec1.OutClockingGuid = ec2.Guid
  JOIN APIHealthcare_Live.dbo.OrganizationUnit ou
      ON ec1.OrganizationUnitID = ou.ID
  LEFT JOIN APIHealthcare_Live.dbo.EmployeePremiumBucket epb
      ON ec1.Guid = epb.TransactionGuid
  JOIN APIHealthcare_Live.dbo.PayCode pc
      ON epb.PayCodeID = pc.ID
  JOIN APIHealthcare_Live.dbo.PayGroupInstance pgi
      ON pgi.ID = epb.PayGroupInstanceID
  WHERE ec1.transactiondate between @StartDate and @EndDate 
  and epb.PaymentClassification = '1'
  AND e.code = @EmployeeCode
  AND e.ArchiveTaskID =
  (
      SELECT MAX(e2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
      WHERE e2.ID = e.ID
  )
  GROUP BY e.Code,
       e.FirstName,
       e.LastName,
       ec1.TransactionDate,
       ou.Code,
       ec1.Classification,
       ec2.Classification,
       pc.code,
       epb.hourvalue
  
  UNION
  SELECT e.Code AS EMPLOYEE_ID,
     e.FirstName AS FIRST_NAME,
     e.LastName AS LAST_NAME,
     ec1.TransactionDate AS DATE_OF_SHIFT,
     ou.Code AS DEPT_UNIT_CODE,
     CASE
         WHEN ec1.Classification = '1' THEN
             MIN(ec1.TransactionDate)
     END AS CLOCK_IN,
     CASE
         WHEN ec2.Classification = '2' THEN
             MAX(ec2.TransactionDate)
     END AS CLOCK_OUT,
     pc.code,
     epb.hourvalue 
  FROM APIHealthcare_History_Live.dbo.Employee e
  JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec1
      ON ec1.EmployeeID = e.ID
  JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec2
      ON ec1.OutClockingGuid = ec2.Guid
  JOIN APIHealthcare_History_Live.dbo.OrganizationUnit ou
      ON ec1.OrganizationUnitID = ou.ID
  LEFT JOIN APIHealthcare_History_Live.dbo.EmployeePremiumBucket epb
      ON ec1.Guid = epb.TransactionGuid
  JOIN APIHealthcare_History_Live.dbo.PayCode pc
      ON epb.PayCodeID = pc.ID
  JOIN APIHealthcare_History_Live.dbo.PayGroupInstance pgi
      ON pgi.ID = epb.PayGroupInstanceID
  WHERE ec1.transactiondate between @StartDate and @EndDate
  and epb.PaymentClassification = '1'
  AND e.code = @EmployeeCode
  AND e.ArchiveTaskID =
  (
      SELECT MAX(e2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
      WHERE e2.ID = e.ID
  )
  AND ou.ArchiveTaskID =
  (
      SELECT MAX(ou2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.OrganizationUnit ou2 (NOLOCK)
      WHERE ou2.ID = ou.ID
  )
  AND pc.ArchiveTaskID =
  (
      SELECT MAX(pc2.ArchiveTaskID)
      FROM APIHealthcare_History_Live.dbo.PayCode pc2 (NOLOCK)
      WHERE pc2.ID = pc.ID
  )
  GROUP BY e.Code,
       e.FirstName,
       e.LastName,
       ec1.TransactionDate,
       ou.Code,
       ec1.Classification,
       ec2.Classification,
       pc.code,
       epb.hourvalue) as X
       
  group by
  EMPLOYEE_ID, 
  FIRST_NAME, 
  LAST_NAME,
  DATE_OF_SHIFT, 
  DEPT_UNIT_CODE,
  CLOCK_IN, 
  CLOCK_OUT, 
  code 
  order by date_of_shift, code 
  END
  </code></pre>