Feature Request / Question - Global Temp Tables

vjacob1

Does SQL Prompt go into global temp tables and if so is it possible to request that feature?

squigley

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?
​

vjacob1

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

squigley

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

vjacob1

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

squigley

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?

vjacob1

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??

squigley

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?

vjacob1

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

squigley

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!