Feature Request / Question - Global Temp Tables

• 
  Morning!

  Thanks for your inquiry with SQL Prompt.

  SQL Prompt should give suggestions to global temp tables. Was there something in particular feature wise that you wanted SQL Prompt to do with Global Temp Tables?
  ​

  
  

  squigley March 17, 2020 23:03

  - 0 +
• 
  I have a temptable built using a dynamic query but it is not displaying the fields inside...this is why I suggested it.

  vjacob1 March 17, 2020 23:46

  - 0 +
• 
  Could you send me an example query where you see this happening so that I can try replicating this on my end?
  

  squigley March 20, 2020 20:36

  - 0 +
• 
  SET NOCOUNT ON;

  
  

  --Clear Temp Tables

  --IF (OBJECT_ID('tempdb..#UnderweightBMI') IS NOT NULL)

  -- DROP TABLE #UnderweightBMI

  
  

  IF (OBJECT_ID('tempdb..##UnderweightBMIPatients') IS NOT NULL)

  DROP TABLE ##UnderweightBMIPatients

  
  

  IF (OBJECT_ID('dbo.UnderweightBMI') IS NOT NULL)

  TRUNCATE TABLE dbo.UnderweightBMI

  
  

  --Dynamic Query Variables

  DECLARE @Columns NVARCHAR(MAX),

  @SQLQuery NVARCHAR(MAX)

  
  

  
  

  IF (@StartDate = @EndDate)

  BEGIN

  
  

  INSERT INTO dbo.UnderweightBMI

  (

      AccountNumber,

      Name,

      LocationID,

  QueryID,

      BMI

  )

  SELECT 

  vnad.AccountNumber,

  vnad.Name,

  vnad.LocationID,

  vnnqr.QueryID,

  [BMI] = CAST(LEFT(vnnqr.Response, CHARINDEX('.', vnnqr.Response) - 1) + '.' + SUBSTRING(vnnqr.Response,(CHARINDEX('.', vnnqr.Response)+1),3) AS DECIMAL(9,1))

  --vnnqr.Response

  --INTO #UnderweightBMI

  FROM livedbNWH.dbo.vNWH_AbstractData vnad 

  JOIN livedbNWH.dbo.vNWH_AdmVisits vnav ON vnad.VisitID = vnav.VisitID

  JOIN livedbNWH.dbo.vNWH_AdmittingData vnad2 ON vnad.VisitID = vnad2.VisitID

  JOIN livedbNWH.dbo.vNWH_NurQueryResults vnnqr ON vnad.VisitID = vnnqr.VisitID

  WHERE 

  ((vnav.InpatientOrOutpatient = 'I' OR vnad.ObservationPatient = 'Y') OR vnav.Status = 'REG SDC')

  AND vnad.DischargeDateTime IS NULL

  --AND vnnqr.QueryID = 'N.BMI' 

  AND vnnqr.QueryID IN ('N.BMI', 'NNUBMI')

  AND CAST(LEFT(vnnqr.Response, CHARINDEX('.', vnnqr.Response) - 1) + '.' + SUBSTRING(vnnqr.Response,(CHARINDEX('.', vnnqr.Response)+1),3) AS DECIMAL(9,1)) < 19.0

  GROUP BY vnad.VisitID,

  vnad.AccountNumber,

  vnad.Name,

  vnad.AdmitDateTime,

  vnad.LocationID,

  vnav.Status,

  vnnqr.QueryID,

  vnnqr.Response

  ORDER BY 

  --vnad.LocationID

  vnad.VisitID

  
  

  END

  
  

  ELSE

      BEGIN

  
  

  INSERT INTO dbo.UnderweightBMI

  (

      AccountNumber,

      Name,

      LocationID,

  QueryID,

      BMI

  )

  SELECT 

  vnad.AccountNumber,

  vnad.Name,

  vnad.LocationID,

  vnnqr.QueryID,

  [BMI] = CAST(LEFT(vnnqr.Response, CHARINDEX('.', vnnqr.Response) - 1) + '.' + SUBSTRING(vnnqr.Response,(CHARINDEX('.', vnnqr.Response)+1),3) AS DECIMAL(9,1))

  --vnnqr.Response

  --INTO #UnderweightBMI

  FROM livedbNWH.dbo.vNWH_AbstractData vnad 

  JOIN livedbNWH.dbo.vNWH_AdmVisits vnav ON vnad.VisitID = vnav.VisitID

  JOIN livedbNWH.dbo.vNWH_AdmittingData vnad2 ON vnad.VisitID = vnad2.VisitID

  JOIN livedbNWH.dbo.vNWH_NurQueryResults vnnqr ON vnad.VisitID = vnnqr.VisitID

  WHERE 

  ((vnav.InpatientOrOutpatient = 'I' OR vnad.ObservationPatient = 'Y') OR vnav.Status = 'REG SDC')

  --AND vnnqr.QueryID = 'N.BMI' 

  AND vnnqr.QueryID IN ('N.BMI', 'NNUBMI')

  AND CAST(LEFT(vnnqr.Response, CHARINDEX('.', vnnqr.Response) - 1) + '.' + SUBSTRING(vnnqr.Response,(CHARINDEX('.', vnnqr.Response)+1),3) AS DECIMAL(9,1)) < 19.0

  AND ((CONVERT(DATE, vnad.AdmitDateTime) BETWEEN CONVERT(DATE, @StartDate) AND CONVERT(DATE, @EndDate))

  OR (CONVERT(DATE, vnad.DischargeDateTime) BETWEEN CONVERT(DATE, @StartDate) AND CONVERT(DATE, @EndDate)))

  GROUP BY vnad.VisitID,

  vnad.AccountNumber,

  vnad.Name,

  vnad.AdmitDateTime,

  vnad.LocationID,

  vnav.Status,

  vnnqr.QueryID,

  vnnqr.Response

  ORDER BY 

  --vnad.LocationID

  vnad.VisitID

  
  

  END

  
  

  --Get QueryIDs for the Pivot Column Names

  SELECT @Columns = STUFF((SELECT Distinct  ',' +   QUOTENAME(ub.QueryID)

  from dbo.UnderweightBMI ub

  group BY ub.QueryID, ub.BMI

  --order by Question  

  FOR XML PATH(''), TYPE

  ).value('.', 'NVARCHAR(MAX)') 

  ,1,1,'')

  
  

  --SELECT @Columns

  /*

  SELECT ub.AccountNumber,

     ub.Name,

     ub.LocationID,

     ub.QueryID,

     ub.BMI 

  FROM #UnderweightBMI ub

  */

  
  

  --Dynamic Pivot SQL Query

  SET @SQLQuery = N'SELECT AccountNumber, Name, LocationID, ' + @Columns + N' INTO ##UnderweightBMIPatients from 

  (

  SELECT ub.AccountNumber,

     ub.Name,

     ub.LocationID,

     ub.QueryID,

     ub.BMI 

  FROM dbo.UnderweightBMI ub         

  ) x

  pivot 

  (

  max(BMI)

  for QueryID in (' + @Columns + N')

  ) p '

  
  

  --SELECT @query;

  
  

  EXEC  sys.sp_executesql @SQLQuery

  
  

  --Output Final Results for Daily Report

  SELECT  

  AccountNumber,

  Name,

  LocationID,

  [BMI] = COALESCE([N.BMI], [NNUBMI])

  FROM ##UnderweightBMIPatients

  vjacob1 March 20, 2020 20:42

  - 0 +
• 
  Thanks for sending that query. I tried running it on my end, but I can't quite get the same behavior on my end.

  Can you go into more detail on how you got this working on your end? Are there any prerequisites that I should be aware of?
  

  
  

  squigley March 25, 2020 22:54

  - 0 +
• 
  The query would not work because the data is different
  The only thing that I can say is that the I am using a dynamic query to fill the global temp table as illustrated in my code snippet.
  Perhaps that is why it is not displaying the fields properly??

  vjacob1 March 26, 2020 00:27

  - 0 +
• 
  Good Morning!
  I tried working on this with my team to replicate this, but we weren't successful.
  If I remove the table name from INSERT INTO, and then try to let Prompt to suggest it, I don't get anything.
  
  ​
  
  another example, Prompt doesn't suggest the table after FROM either
  
  

  ​

  Can you send a more detailed step by step procedure on how you created the global temp table using the dynamic query and then how you attempted to have SQL Prompt work with it in order to help with the replication?
  

  
  

  squigley April 01, 2020 16:32

  - 0 +
• 
  The step by step procedure on how the global temp table is the code I sent
  I did not create the table ahead of time - I am just running a select statement and insert into the global temp table.
  I just want SQL PRompt to be able to display the columns in that table as it is a pain to manually have to type them in the code
  
  

  vjacob1 April 01, 2020 17:21

  - 0 +
• 
  Good Afternoon!
  

  Thanks for the clarification on your end.

  The team and I were able to look further into it and can confirm that as of this current time this isn't something that SQL Prompt can do.

  
  

  The problem here is that the table is created in a dynamic SQL Query, so SQL Prompt doesn't know anything about it. SQL Prompt needs to see the creation code in the query before it will do this.
  
  However, we don't parse the dynamic SQL or format it. Apologies for the inconvenience that this brings. If you would like for this to become a feature, I would highly recommend creating a uservoice request so that the developers can see the demand for such a feature.
  

  
  

  SQL Prompt Uservoice:
  

  https://redgate.uservoice.com/forums/94413-sql-prompt

  
  

  Please let me know if you have any other questions or concerns.
  
  Thanks!
  

  squigley April 07, 2020 22:23

  - 0 +

Add comment

Please sign in to leave a comment.