Feature Request / Question - Global Temp Tables

vjacob1vjacob1 Posts: 57 Bronze 1
Does SQL Prompt go into global temp tables and if so is it possible to request that feature?

Answers

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

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





  • vjacob1vjacob1 Posts: 57 Bronze 1
    I have a temptable built using a dynamic query but it is not displaying the fields inside...this is why I suggested it.
  • squigleysquigley Posts: 227 Gold 1
    edited March 20, 2020 8:36PM
    Could you send me an example query where you see this happening so that I can try replicating this on my end?

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





  • vjacob1vjacob1 Posts: 57 Bronze 1
    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)


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

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





  • vjacob1vjacob1 Posts: 57 Bronze 1
    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??
  • 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?

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





  • vjacob1vjacob1 Posts: 57 Bronze 1
    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

  • 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:

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

    Thanks!

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





Sign In or Register to comment.