none
Select Column's Name in Stored Proc. RRS feed

  • Question

  • @All

    I have complex stored procedure to selecting the student details.

    In normal selection of the column names in  stored procedure, i can using "sp_describe_first_result_set".

    but, here, i'm using various temp table and other things. when, i'm using the "sp_describe_first_result_set", it tries to select first which i have written in insert statement. 

    can anyone assist me to solve this issue?

    SP:

    USE [JourneyReports]
    GO
    /****** Object:  StoredProcedure [dbo].[JSSRS_AL_Electrical_Contractors]    Script Date: 17-09-2019 12.25.48 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ---- =============================================    
    ---- Author:  TechAffinity  
    ---- Create date: 08/29/2019    
    ---- Description: Accreditation report for Journey Internal Reporting project  
    ---- HelpText :  [dbo].[JSSRS_AL_Electrical_Contractors] '2019-06-01 00:00:00.000','7/1/2019','964BE6AF-C3EF-4A0A-A675-E2832D2F266E','RV-PGM168'  
    ---- =============================================    
    ALTER PROCEDURE [dbo].[JSSRS_AL_Electrical_Contractors]    
    (    
     @startDate datetime =null,      
     @endDate datetime = null,
     @licenseUUID uniqueIdentifier = '964BE6AF-C3EF-4A0A-A675-E2832D2F266E',
     @vector_course_ids nvarchar(max) = 'RV-PGM168'
    )
    AS
    BEGIN
     --to ensure start date is at midnight  
     SET @startDate = DATEADD(day, DATEDIFF(DAY, 1, @startDate) - 1, 1)  
     --to ensure all of end date completions are accounted for adding day and subtracting second  
     SET @endDate = DATEADD(SECOND, -1,DATEADD(DAY, 1,CONVERT(VARCHAR, @endDate, 101))- 1)  

    -- create the temp table to hold all applicable 
    CREATE TABLE #CourseApprovals
    (
    CourseUUID UNIQUEIDENTIFIER,
    CourseApprovalNumber NVARCHAR(510),
    ProviderID UNIQUEIDENTIFIER,
    MinimumSeatTime INT,
    LicenseScore DECIMAL(10,2),
    EffectiveDate DateTime,
    ExpiresDate DateTime,
    RenewalExpireDate DATETIME
    )

    -- get all the applicable course approvals
    INSERT INTO #CourseApprovals 
    SELECT * FROM fnc_Users_AccredLicenseCourseCompletions_GetCourseApprovals(@startdate, @endDate, @licenseUUID)

    -- accreditation report specific select
     SELECT 
       CUI.CourseInstanceUUID AS course_unit_attempt_id,
       U.UserUUID AS account_user_id,
       U.FirstName AS first_name,
       U.LastName AS last_name,
       UL.LicenseNumber AS license_nbr,
       C.coursename AS course_title,
       (CUI.completiondate) AS completion_date,
       C.CourseHours AS course_hours,
       ca.LicenseScore AS LicenseScore,
       ca.MinimumSeatTime * 60 AS LicenseMinSeatTime,
       cui.score AS CourseScore,
       cui.TotalSecondsinInstance AS CourseTotalSecondsInInstance,
       (CUI.completiondate) AS courseCompletionDate
    INTO #CourseUserInstance
    FROM
       [dbo].[CourseUserInstance] CUI 
       INNER JOIN #CourseApprovals ca
    ON ca.CourseUUID = cui.CourseUUID 
       INNER JOIN
          [dbo].[Courses] C WITH(NOLOCK)
          ON C.courseuuid = CUI.CourseUUID 
      AND c.Version = CUI.Version
       INNER JOIN
          [dbo].[Users] U WITH(NOLOCK) 
          ON U.UserUUID = CUI.UserUUID 
       INNER JOIN   
      [dbo].[Users_AccredLicenses] UL WITH(NOLOCK)   
      ON U.UserUUID = UL.UserUUID  AND UL.AccredLicenseID = @licenseUUID

    WHERE
    CUI.completiondate IS NOT NULL  AND CUI.status  = 3 -- get passed student details only
    AND
       C.VectorCourseID IN 
       (
      SELECT
    item 
      FROM
    dbo.fnc_10_comma_delimited_nVarChar(@vector_course_ids, ',')
       )  

    -- get the completion dates
    UPDATE #CourseUserInstance
    SET completion_date = completionDate
    FROM #CourseUserInstance CUI
    CROSS APPLY  dbo.fnc_Users_AccredLicenseCourseCompletions_GetLicenseCompletion(course_unit_attempt_id, LicenseScore, LicenseMinSeatTime) 

    -- remove all records that do not have a completion date sonce this report only wants completed courses
    DELETE FROM #CourseUserInstance WHERE completion_date IS NULL OR (completion_date > @endDate OR completion_date < @startDate)

    --SET FMTONLY ON;
    -- final select 
    SELECT DISTINCT course_unit_attempt_id,
                    account_user_id,
                    first_name,
                    last_name,
                    license_nbr,
                    course_title,
                    completion_date,
                    course_hours,
                    LicenseScore,
                    LicenseMinSeatTime,
                    CourseScore,
                    CourseTotalSecondsInInstance,
                    courseCompletionDate FROM #CourseUserInstance
    --SET FMTONLY OFF;
    END    


    Monday, September 16, 2019 12:57 PM

All replies

  • I am not sure what the issue is. What are you trying to achieve?

    Generally, sp_describe_first_result_set will not suceed if there are temp tables involved.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 16, 2019 9:48 PM
  • Hi Elango,

    Could you please show us your stored procedure? It's hard to imagine your problem. 

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 17, 2019 2:41 AM
  • In above, i have attached Stored Procedure. Please find the same and advice me further?
    Tuesday, September 17, 2019 7:09 AM
  • Yes, i know that. Is there any other ways to find the column name's in above procedure? Kindly advice on the same

    Tuesday, September 17, 2019 7:11 AM
  • What this one using sys.dm_exec_describe_first_result_set_for_object?

    ----Get Stored procedure result into a table
     
    ;with mycte as (
    SELECT distinct p.name, r.column_ordinal,r.name colName,r.system_type_name,r.is_nullable 
    FROM sys.procedures AS p
    CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
    WHERE p.name not like'sp_%' --no system sp
    and p.name ='getYourSpName' 
    )
     
      
     
      SELECT   N'If Object_id('''+QUOTENAME('Show_SpResult_'+m1.name)+''',''U'') is not null DROP Table '
      + QUOTENAME('Show_SpResult_'+m1.name) 
      + ' Create table '
      + QUOTENAME('Show_SpResult_'+m1.name) 
      +'('+ STUFF((SELECT  ', ' + QUOTENAME(Colname) + ' '
      + system_type_name + Case when is_nullable=1 then  ' NULL' Else ' Not NULL' END
          FROM mycte AS m2
          WHERE m1.name=m2.name
          ORDER BY m2.column_ordinal
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
          +');  INSERT INTO '+QUOTENAME('Show_SpResult_'+m1.name)+ ' EXEC '+ Name
          + ';  SELECT * FROM '+QUOTENAME('Show_SpResult_'+m1.name) +';' as QueryToBeExecuted
     FROM mycte AS m1
     Group by name

    Tuesday, September 17, 2019 4:13 PM
    Moderator
  • One solution is to wrap the final SELECT in dynamic SQL and then use the WITH RESULT SETS clause:

    EXEC ('SELECT DISTINCT course_unit_attempt_id,
                    account_user_id,
                    first_name,
                    last_name,
                    license_nbr,
                    course_title,
                    completion_date,
                    course_hours,
                    LicenseScore,
                    LicenseMinSeatTime,
                    CourseScore,
                    CourseTotalSecondsInInstance,
                    courseCompletionDate FROM #CourseUserInstance')
       WITH RESULT SETS (...)

    In the WITH RESULT SETS clause you specify the shape of the result set. Please see Books Online for the syntax.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, September 17, 2019 9:45 PM
  • @All

    I have complex stored procedure to selecting the student details.

    In normal selection of the column names in  stored procedure, i can using "sp_describe_first_result_set".

    but, here, i'm using various temp table and other things. when, i'm using the "sp_describe_first_result_set", it tries to select first which i have written in insert statement. 

    can anyone assist me to solve this issue?

    SP:

    USE [JourneyReports]
    GO
    /****** Object:  StoredProcedure [dbo].[JSSRS_AL_Electrical_Contractors]    Script Date: 17-09-2019 12.25.48 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ---- =============================================    
    ---- Author:  TechAffinity  
    ---- Create date: 08/29/2019    
    ---- Description: Accreditation report for Journey Internal Reporting project  
    ---- HelpText :  [dbo].[JSSRS_AL_Electrical_Contractors] '2019-06-01 00:00:00.000','7/1/2019','964BE6AF-C3EF-4A0A-A675-E2832D2F266E','RV-PGM168'  
    ---- =============================================    
    ALTER PROCEDURE [dbo].[JSSRS_AL_Electrical_Contractors]    
    (    
     @startDate datetime =null,      
     @endDate datetime = null,
     @licenseUUID uniqueIdentifier = '964BE6AF-C3EF-4A0A-A675-E2832D2F266E',
     @vector_course_ids nvarchar(max) = 'RV-PGM168'
    )
    AS
    BEGIN
     --to ensure start date is at midnight  
     SET @startDate = DATEADD(day, DATEDIFF(DAY, 1, @startDate) - 1, 1)  
     --to ensure all of end date completions are accounted for adding day and subtracting second  
     SET @endDate = DATEADD(SECOND, -1,DATEADD(DAY, 1,CONVERT(VARCHAR, @endDate, 101))- 1)  

    -- create the temp table to hold all applicable 
    CREATE TABLE #CourseApprovals
    (
    CourseUUID UNIQUEIDENTIFIER,
    CourseApprovalNumber NVARCHAR(510),
    ProviderID UNIQUEIDENTIFIER,
    MinimumSeatTime INT,
    LicenseScore DECIMAL(10,2),
    EffectiveDate DateTime,
    ExpiresDate DateTime,
    RenewalExpireDate DATETIME
    )

    -- get all the applicable course approvals
    INSERT INTO #CourseApprovals 
    SELECT * FROM fnc_Users_AccredLicenseCourseCompletions_GetCourseApprovals(@startdate, @endDate, @licenseUUID)

    -- accreditation report specific select
     SELECT 
       CUI.CourseInstanceUUID AS course_unit_attempt_id,
       U.UserUUID AS account_user_id,
       U.FirstName AS first_name,
       U.LastName AS last_name,
       UL.LicenseNumber AS license_nbr,
       C.coursename AS course_title,
       (CUI.completiondate) AS completion_date,
       C.CourseHours AS course_hours,
       ca.LicenseScore AS LicenseScore,
       ca.MinimumSeatTime * 60 AS LicenseMinSeatTime,
       cui.score AS CourseScore,
       cui.TotalSecondsinInstance AS CourseTotalSecondsInInstance,
       (CUI.completiondate) AS courseCompletionDate
    INTO #CourseUserInstance
    FROM
       [dbo].[CourseUserInstance] CUI 
       INNER JOIN #CourseApprovals ca
    ON ca.CourseUUID = cui.CourseUUID 
       INNER JOIN
          [dbo].[Courses] C WITH(NOLOCK)
          ON C.courseuuid = CUI.CourseUUID 
      AND c.Version = CUI.Version
       INNER JOIN
          [dbo].[Users] U WITH(NOLOCK) 
          ON U.UserUUID = CUI.UserUUID 
       INNER JOIN   
      [dbo].[Users_AccredLicenses] UL WITH(NOLOCK)   
      ON U.UserUUID = UL.UserUUID  AND UL.AccredLicenseID = @licenseUUID

    WHERE
    CUI.completiondate IS NOT NULL  AND CUI.status  = 3 -- get passed student details only
    AND
       C.VectorCourseID IN 
       (
      SELECT
    item 
      FROM
    dbo.fnc_10_comma_delimited_nVarChar(@vector_course_ids, ',')
       )  

    -- get the completion dates
    UPDATE #CourseUserInstance
    SET completion_date = completionDate
    FROM #CourseUserInstance CUI
    CROSS APPLY  dbo.fnc_Users_AccredLicenseCourseCompletions_GetLicenseCompletion(course_unit_attempt_id, LicenseScore, LicenseMinSeatTime) 

    -- remove all records that do not have a completion date sonce this report only wants completed courses
    DELETE FROM #CourseUserInstance WHERE completion_date IS NULL OR (completion_date > @endDate OR completion_date < @startDate)

    --SET FMTONLY ON;
    -- final select 
    SELECT DISTINCT course_unit_attempt_id,
                    account_user_id,
                    first_name,
                    last_name,
                    license_nbr,
                    course_title,
                    completion_date,
                    course_hours,
                    LicenseScore,
                    LicenseMinSeatTime,
                    CourseScore,
                    CourseTotalSecondsInInstance,
                    courseCompletionDate FROM #CourseUserInstance
    --SET FMTONLY OFF;
    END    


    Finally we done it. Making the Temp Table into Table Variable....
    Tuesday, September 24, 2019 1:11 PM
  • Hi Elango,

    We are glad to hear that these posts are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 26, 2019 2:54 AM