3

I need to find the column definitions from a stored procedure.The docs for sp_describe_first_result_set show an entry for user_type_name where I'd expect to see my User Defined Types. Sadly, data for UDTs seems to be missing from the procedure result. Given the sample code:

CREATE PROCEDURE [dbo].[test]
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @test TABLE (
        a BIGINT
        ,b udtTest
        ,c NVARCHAR(100)
        ,d BIT
    )

    INSERT INTO @test (
        a, b, c, d
    ) VALUES (
        1, 'udtTest', 'nvarchar', 0
    )

    select *
    from @test
END
GO
EXEC [dbo].[sp_describe_first_result_set] @tsql = N'test';
GO

I'm seeing the following (db<>fiddle):

name system_type_name user_type_name
a bigint NULL
b nvarchar(123) NULL
c nvarchar(100) NULL
d bit NULL

Why is udtTest missing from my data for column name b?

3
  • 2
    FWIW, I can repro with the table variable like your example but it works as expected with a permanent table. Maybe someone can explain why.
    – Dan Guzman
    Apr 6 '21 at 14:58
  • Interesting question. Consider the differences between table variables, temp tables, and permanent tables (see dbfiddle.uk/…). Would you care to edit some of that detail into your question (or would you mind if I take the liberty of doing so)? Note also the availability of sys.dm_exec_describe_first_result_set for a narrower result set while rubber-ducking Apr 7 '21 at 9:57
  • @PeterVandivier feel free to improve up my question
    – Mark
    Apr 8 '21 at 6:37
4

This appears not to work as advertised. Temporary tables are disallowed, but table variables should work just fine. Alias types are a bit of an edge case, so perhaps a bug crept in here unnoticed. If it is a blocker for you, open a support case with Microsoft.

There aren't any perfect workarounds (that I am aware of). One of the best ones is to create a wrapper procedure that uses WITH RESULT SETS to define the output shape:

CREATE TYPE dbo.udtTest FROM nvarchar(123) NOT NULL;
GO
CREATE OR ALTER PROCEDURE dbo.P AS
SET NOCOUNT ON;
DECLARE @T TABLE (udt dbo.udtTest);
INSERT @T VALUES (N'Banana');
SELECT T.udt FROM @T AS T;
GO
-- No user type info
EXECUTE sys.sp_describe_first_result_set 
    @tsql = N'EXECUTE Sandpit.dbo.P;', 
    @parameters = NULL, 
    @browse_information_mode = 0;
-- Wrapper for dbo.P
CREATE OR ALTER PROCEDURE dbo.P_Wrapper AS
SET NOCOUNT ON;
EXECUTE dbo.P 
    WITH RESULT SETS ((udt dbo.udtTest NOT NULL));
GO
-- Works!
EXECUTE sys.sp_describe_first_result_set 
    @tsql = N'EXECUTE dbo.P_Wrapper;', 
    @parameters = NULL, 
    @browse_information_mode = 0;

This may or may not be convenient or practical for you. It's probably the best we can do until the bug (?) is fixed, or Microsoft extend CREATE PROCEDURE syntax to include WITH RESULT SETS, as we have been asking for a long time now.

1
  • Thanks for your help. It is indeed not a practical solution in my case. I'll keep on digging and consider your solution as last chance.
    – Mark
    Apr 8 '21 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.