none
Can I JOIN/UNION a CTE to a stored procedure?

    Question

  • Please I think this is possible but not sure what I'm doing wrong:

    CREATE PROCEDURE [dbo].[sp_PackagePerformance]

    AS

    CREATE TABLE #PackageGroup1
    (PackageGroup VARCHAR(128))

    INSERT INTO #PackageGroup1
            ( PackageGroup )
    (SELECT DISTINCT PackageGroup FROM dbo.SSIS_ExecutionLog)


    BEGIN
    DECLARE @DateBegin_prt1 varchar(50)
    , @DateBegin_prt2 varchar(50) = '04:30:00.000'
    , @FullDateBegin varchar(50)
    , @FullDateEnd varchar(50)

    SET @DateBegin_prt1 = (SELECT CONVERT(VARCHAR(11),MAX(LogTime),120) FROM dbo.SSIS_ExecutionLog)
    SET @FullDateBegin = @DateBegin_prt1 + '' + @DateBegin_prt2 
    SET @FullDateBegin = cast(@FullDateBegin as datetime)
    SET @FullDateEnd = DateAdd(hour,-15,@FullDateBegin)


    SELECT E.PackageGroup
    , E.PackageName
    , E.TaskName
    ,       right('0' + rtrim(convert(char(2), Duration_in_sec / (60 * 60))), 2) + ':' + 
            right('0' + rtrim(convert(char(2), (Duration_in_sec / 60) % 60)), 2) + ':' + 
            right('0' + rtrim(convert(char(2), Duration_in_sec % 60)),2) Duration
    , E.ExecutionID
    , E.EndLogTime
    , E.ContainerID
    , (ISNULL(ER.[restart],0)) [Restarts]
    , ER.ErrorDescription
    , ER.PackageID
    FROM

    ((SELECT Distinct a.PackageGroup
    , a.PackageName
    , a.TaskName
    , datediff(second,b.LogTime, a.LogTime) DURATION_in_sec
    , a.ExecutionID
    , a.LogTime EndLogTime
    , a.ContainerID
    FROM

    (SELECT PG.[PackageGroup]
    ,[PackageName]
    ,[TaskName]
    ,[ExecutionID]
    ,[LogStatus]
    ,[LogTime] 
    ,[ContainerID]
    FROM [SSIS_Data].[dbo].[SSIS_ExecutionLog] EL
    INNER JOIN #PackageGroup1 PG
    ON EL.PackageGroup = PG.PackageGroup
    AND LogStatus = 'Run'
    GROUP BY PG.PackageGroup, PackageID, PackageName, TaskName, ExecutionID, ExecStage, Logstatus, LogTime,ContainerID) a INNER JOIN 

    (SELECT PG.[PackageGroup]
    ,[PackageName]
    ,[TaskName]
    ,[ExecutionID]
    ,[LogStatus]
    ,[LogTime] 
    ,[ContainerID]
    FROM [SSIS_Data].[dbo].[SSIS_ExecutionLog] EL
    INNER JOIN #PackageGroup1 PG
    ON EL.PackageGroup = PG.PackageGroup
    AND LogStatus = 'Run'
    GROUP BY PG.PackageGroup, PackageID, PackageName, TaskName, ExecutionID, ExecStage, Logstatus, LogTime,ContainerID) b

    ON
    a.ExecutionID = b.ExecutionID
    where a.LogTime < @FullDateBegin and a.LogTime > @FullDateEnd
    and datediff(second,b.LogTime, a.LogTime) > 0
    )) E


    Left JOIN
    (SELECT TaskName, [Restart], ErrorDescription, PackageID
    FROM
    [dbo].[SSIS_ExecutionLog_ErrorsRestart]) ER
    ON E.TaskName = ER.TaskName

    GROUP BY e.PackageGroup,e.PackageName, e.TaskName, e.DURATION_in_sec,E.ExecutionID
    , E.EndLogTime
    , E.ContainerID
    ,ER.ErrorDescription
    , ER.PackageID
    , ER.[restart]

    UNION 


    IF EXISTS 
    (SELECT * FROM tempdb.dbo.sysobjects 
    WHERE ID= OBject_ID(N'tempdb..#Temp1')
    )
    Begin
    DROP TABLE #Temp1
    END

    ;With PackageGroupDuration
    ( JobName,
    PackageGroup, 
    LogDate)
    AS
    (SELECT p.JobName, p.PackageGroup, Max(j.[Run_PT]) LogDate
    FROM ssis_data.[dbo].[PackageGroups] P Inner JOIN
    Util.[dbo].[v_sqlAgentJobHistory] J ON
    p.JobName = j.[Job Name]
    GROUP BY p.JobName, p.PackageGroup)

    SELECT JobName, PackageGroup, LogDate
    INTO #Temp1
    FROM PackageGroupDuration

    SELECT t.*, a.[run_minutes] GroupDuration
    FROM #Temp1 t
    Left JOIN
    util.[dbo].[v_sqlAgentJobHistory] a
    ON a.[Job Name] = t.JobName AND
    t.LogDate = a.run_PT



    END

    Wednesday, April 03, 2013 10:47 PM

Answers

  • Hi J,

          You cant union two sql statements if the number of columns and the datatypes are different. However, I changed your query in few places for you as below. You can take the below as a starting point.

    CREATE PROCEDURE [dbo].[sp_PackagePerformance]
    AS
    BEGIN
    CREATE TABLE #PackageGroup1
    (PackageGroup VARCHAR(128))
    INSERT INTO #PackageGroup1
            ( PackageGroup )
    (SELECT DISTINCT PackageGroup FROM dbo.SSIS_ExecutionLog)
    DECLARE @DateBegin_prt1 varchar(50)
    , @DateBegin_prt2 varchar(50) = '04:30:00.000'
    , @FullDateBegin varchar(50)
    , @FullDateEnd varchar(50)
    SET @DateBegin_prt1 = (SELECT CONVERT(VARCHAR(11),MAX(LogTime),120) FROM dbo.SSIS_ExecutionLog)
    SET @FullDateBegin = @DateBegin_prt1 + '' + @DateBegin_prt2 
    SET @FullDateBegin = cast(@FullDateBegin as datetime)
    SET @FullDateEnd = DateAdd(hour,-15,@FullDateBegin)
    ;With PackageGroupDuration
    ( JobName,
    PackageGroup, 
    LogDate)
    AS
    (SELECT p.JobName, p.PackageGroup, Max(j.[Run_PT]) LogDate
    FROM ssis_data.[dbo].[PackageGroups] P Inner JOIN
    Util.[dbo].[v_sqlAgentJobHistory] J ON
    p.JobName = j.[Job Name]
    GROUP BY p.JobName, p.PackageGroup)
    SELECT JobName, PackageGroup, LogDate
    INTO #Temp1
    FROM PackageGroupDuration
    SELECT /*t.*, a.[run_minutes] GroupDuration*/
    --<<You need to match the same column as the second query in the number and datatype>>
    FROM #Temp1 t
    Left JOIN
    util.[dbo].[v_sqlAgentJobHistory] a
    ON a.[Job Name] = t.JobName AND
    t.LogDate = a.run_PT
    Union
    SELECT E.PackageGroup
    , E.PackageName
    , E.TaskName
    ,       right('0' + rtrim(convert(char(2), Duration_in_sec / (60 * 60))), 2) + ':' + 
            right('0' + rtrim(convert(char(2), (Duration_in_sec / 60) % 60)), 2) + ':' + 
            right('0' + rtrim(convert(char(2), Duration_in_sec % 60)),2) Duration
    , E.ExecutionID
    , E.EndLogTime
    , E.ContainerID
    , (ISNULL(ER.[restart],0)) [Restarts]
    , ER.ErrorDescription
    , ER.PackageID
    FROM
    ((SELECT Distinct a.PackageGroup
    , a.PackageName
    , a.TaskName
    , datediff(second,b.LogTime, a.LogTime) DURATION_in_sec
    , a.ExecutionID
    , a.LogTime EndLogTime
    , a.ContainerID
    FROM
    (SELECT PG.[PackageGroup]
    ,[PackageName]
    ,[TaskName]
    ,[ExecutionID]
    ,[LogStatus]
    ,[LogTime] 
    ,[ContainerID]
    FROM [SSIS_Data].[dbo].[SSIS_ExecutionLog] EL
    INNER JOIN #PackageGroup1 PG
    ON EL.PackageGroup = PG.PackageGroup
    AND LogStatus = 'Run'
    GROUP BY PG.PackageGroup, PackageID, PackageName, TaskName, ExecutionID, ExecStage, Logstatus, LogTime,ContainerID) a INNER JOIN 
    (SELECT PG.[PackageGroup]
    ,[PackageName]
    ,[TaskName]
    ,[ExecutionID]
    ,[LogStatus]
    ,[LogTime] 
    ,[ContainerID]
    FROM [SSIS_Data].[dbo].[SSIS_ExecutionLog] EL
    INNER JOIN #PackageGroup1 PG
    ON EL.PackageGroup = PG.PackageGroup
    AND LogStatus = 'Run'
    GROUP BY PG.PackageGroup, PackageID, PackageName, TaskName, ExecutionID, ExecStage, Logstatus, LogTime,ContainerID) b
    ON
    a.ExecutionID = b.ExecutionID
    where a.LogTime < @FullDateBegin and a.LogTime > @FullDateEnd
    and datediff(second,b.LogTime, a.LogTime) > 0
    )) E
    Left JOIN
    (SELECT TaskName, [Restart], ErrorDescription, PackageID
    FROM
    [dbo].[SSIS_ExecutionLog_ErrorsRestart]) ER
    ON E.TaskName = ER.TaskName
    GROUP BY e.PackageGroup,e.PackageName, e.TaskName, e.DURATION_in_sec,E.ExecutionID
    , E.EndLogTime
    , E.ContainerID
    ,ER.ErrorDescription
    , ER.PackageID
    , ER.[restart]
     
    END 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Jstrack13 Thursday, April 04, 2013 12:31 PM
    Thursday, April 04, 2013 11:28 AM

All replies

  • You can't do a UNION followed by a check to drop/create a table.  I can't figure out what you are trying to accomplish

    Chuck Pedretti | Magenic – North Region | magenic.com

    Wednesday, April 03, 2013 11:38 PM
  • >> I think this is possible but not sure what I'm doing wrong: <<

    Mind telling us us what you are trying to do and showing us some DDL? What you did show us has problems with data types, tibbling, data elements change names from table to table, etc. 

    We never create table on the fly in good SQL; that is how you old COBOL programmers mimic scratch tapes. 

    Look at how you waster disk and time with #PackageGroup1; this is a scratch tape for COBOL, but written in T-SQL! We would put the expression in the code where it is used, so the optimizer can use it. 

    (SELECT package_grp, package_name, task_name, execution_id, log_status,
            log_time, container_id
       FROM SSIS_ExecutionLog) AS EL
     INNER JOIN 
     (SELECT DISTINCT package_grp 
        FROM SSIS_ExecutionLog) AS PG
     ON EL.package_grp = PG.package_grp
         AND log_status = 'Run') 

    Now the redundancy is obvious. 

    You made the same error again:

    -- mount a scratch tape and waste disk: 
    SELECT job_name, package_grp, log_date
    INTO #Temp1
    FROM Package_Group_Duration

    --- destroy optimization
    SELECT T.*, A.group_duration
    FROM #Temp1 AS T  
          LEFT OUTER JOIN
          SQL_Agent_Job_History AS A
          ON A.job_name = T.job_name 
             AND T.log_date = A.run_pt –- 2 names but 1 data element! 

    This should have been: 

    SELECT T.*, A.group_duration
      FROM (SELECT job_name, package_grp, log_date
              FROM Package_Group_Duration) AS T
                   LEFT OUTER JOIN
                   SQL_Agent_Job_History AS A
                   ON A.job_name = T.job_name 
                   AND T.log_date = A.log_date);

    Declarative and functional programming does not use local variables. The magic VARCHAR(50) is a sure sign of bad code AND when you use it for temporal data. This is like 1960's COBOL where everything is in strings. In fact, putting the comma at the start of each line (aka punch card) is exactly how we wrote COBOL back them! 

    You named your aliases A, B, etc because that is how tape drives were named in the 1950's. This carried over to the drives on a PC. But an SQL programmer would have used a meaningful descriptive name instead. 

    My guess is that you want to get the duration of some jobs from a log table and that can be done in one query using LAG() functions to match start and end timestamps. Bur we have no DDL or specs or sample data. Do you want to try again and follow basic Netiquette this time?  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 04, 2013 3:22 AM
  • FORGIVE ME CELKO!! I thought that asking a question within  a form of more qualified people was a resource for knowledge. I had no idea it was means for those who have years and year of experience to badger or be-little someone who is completely green as myself. I wasn't alive in 1950 nor do I know what a tape driver is. This a query that I am using for an SSRS report. It does not need to extensive to be useful.  In it's current state it does execute but I get two result sets. All I was asking is how can I combine the CTE and the Stor Proc so that I have ONE result set. 

    Perhaps that drop table is not in the right spot... I DON'T KNOW>. thus my reason for reaching out for help. You cannot assume that everyone who asks a question has is at the same skill level as you. 

    Now is there anyone who would be willing to help explain my error's? Please and Thank you.

    Thursday, April 04, 2013 11:09 AM
  • Hi J,

          You cant union two sql statements if the number of columns and the datatypes are different. However, I changed your query in few places for you as below. You can take the below as a starting point.

    CREATE PROCEDURE [dbo].[sp_PackagePerformance]
    AS
    BEGIN
    CREATE TABLE #PackageGroup1
    (PackageGroup VARCHAR(128))
    INSERT INTO #PackageGroup1
            ( PackageGroup )
    (SELECT DISTINCT PackageGroup FROM dbo.SSIS_ExecutionLog)
    DECLARE @DateBegin_prt1 varchar(50)
    , @DateBegin_prt2 varchar(50) = '04:30:00.000'
    , @FullDateBegin varchar(50)
    , @FullDateEnd varchar(50)
    SET @DateBegin_prt1 = (SELECT CONVERT(VARCHAR(11),MAX(LogTime),120) FROM dbo.SSIS_ExecutionLog)
    SET @FullDateBegin = @DateBegin_prt1 + '' + @DateBegin_prt2 
    SET @FullDateBegin = cast(@FullDateBegin as datetime)
    SET @FullDateEnd = DateAdd(hour,-15,@FullDateBegin)
    ;With PackageGroupDuration
    ( JobName,
    PackageGroup, 
    LogDate)
    AS
    (SELECT p.JobName, p.PackageGroup, Max(j.[Run_PT]) LogDate
    FROM ssis_data.[dbo].[PackageGroups] P Inner JOIN
    Util.[dbo].[v_sqlAgentJobHistory] J ON
    p.JobName = j.[Job Name]
    GROUP BY p.JobName, p.PackageGroup)
    SELECT JobName, PackageGroup, LogDate
    INTO #Temp1
    FROM PackageGroupDuration
    SELECT /*t.*, a.[run_minutes] GroupDuration*/
    --<<You need to match the same column as the second query in the number and datatype>>
    FROM #Temp1 t
    Left JOIN
    util.[dbo].[v_sqlAgentJobHistory] a
    ON a.[Job Name] = t.JobName AND
    t.LogDate = a.run_PT
    Union
    SELECT E.PackageGroup
    , E.PackageName
    , E.TaskName
    ,       right('0' + rtrim(convert(char(2), Duration_in_sec / (60 * 60))), 2) + ':' + 
            right('0' + rtrim(convert(char(2), (Duration_in_sec / 60) % 60)), 2) + ':' + 
            right('0' + rtrim(convert(char(2), Duration_in_sec % 60)),2) Duration
    , E.ExecutionID
    , E.EndLogTime
    , E.ContainerID
    , (ISNULL(ER.[restart],0)) [Restarts]
    , ER.ErrorDescription
    , ER.PackageID
    FROM
    ((SELECT Distinct a.PackageGroup
    , a.PackageName
    , a.TaskName
    , datediff(second,b.LogTime, a.LogTime) DURATION_in_sec
    , a.ExecutionID
    , a.LogTime EndLogTime
    , a.ContainerID
    FROM
    (SELECT PG.[PackageGroup]
    ,[PackageName]
    ,[TaskName]
    ,[ExecutionID]
    ,[LogStatus]
    ,[LogTime] 
    ,[ContainerID]
    FROM [SSIS_Data].[dbo].[SSIS_ExecutionLog] EL
    INNER JOIN #PackageGroup1 PG
    ON EL.PackageGroup = PG.PackageGroup
    AND LogStatus = 'Run'
    GROUP BY PG.PackageGroup, PackageID, PackageName, TaskName, ExecutionID, ExecStage, Logstatus, LogTime,ContainerID) a INNER JOIN 
    (SELECT PG.[PackageGroup]
    ,[PackageName]
    ,[TaskName]
    ,[ExecutionID]
    ,[LogStatus]
    ,[LogTime] 
    ,[ContainerID]
    FROM [SSIS_Data].[dbo].[SSIS_ExecutionLog] EL
    INNER JOIN #PackageGroup1 PG
    ON EL.PackageGroup = PG.PackageGroup
    AND LogStatus = 'Run'
    GROUP BY PG.PackageGroup, PackageID, PackageName, TaskName, ExecutionID, ExecStage, Logstatus, LogTime,ContainerID) b
    ON
    a.ExecutionID = b.ExecutionID
    where a.LogTime < @FullDateBegin and a.LogTime > @FullDateEnd
    and datediff(second,b.LogTime, a.LogTime) > 0
    )) E
    Left JOIN
    (SELECT TaskName, [Restart], ErrorDescription, PackageID
    FROM
    [dbo].[SSIS_ExecutionLog_ErrorsRestart]) ER
    ON E.TaskName = ER.TaskName
    GROUP BY e.PackageGroup,e.PackageName, e.TaskName, e.DURATION_in_sec,E.ExecutionID
    , E.EndLogTime
    , E.ContainerID
    ,ER.ErrorDescription
    , ER.PackageID
    , ER.[restart]
     
    END 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Jstrack13 Thursday, April 04, 2013 12:31 PM
    Thursday, April 04, 2013 11:28 AM
  • Thank you for your help, 

    I ended up dumping the stored procedure into another temp table and joining the two temps

    Thursday, April 04, 2013 12:32 PM