Function return values

Question

Post reply

Function return values

Matthew Saggers-700210

SSC Enthusiast

Points: 183
More actions
April 26, 2017 at 4:47 am

#342612

I have a function that returns a table of values and i like a way to list those values in a query so can see if a field exists
returns @WorkList Table
(
        rowid                Int identity,
        SiteDetailID        UNIQUEIDENTIFIER,
        JobNumber            Varchar(20),
        JobName                Varchar(100)
)
as a list of names and types
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

What is your question exactly?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Matthew Saggers-700210 SSC Enthusiast Points: 183 More actions · Answer 2

in a function I can return a table variable or datatype as shown above if I do the same in a stored procedure I can use sys.dm_exec_describe_first_result_set to tell me what the table structure is that will be returned can I run the same thing for a function to see the output i.e.

rowid Int identity,
SiteDetailID UNIQUEIDENTIFIER,
JobNumber Varchar(20),
JobName Varchar(100)

as a list that I can check for a column name

thanks

Matt

Phil Parkin SSC Guru Points: 246991 More actions · Answer 3

Maybe you could query
INFORMATION_SCHEMA.ROUTINE_COLUMNS?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101130 More actions · Answer 4

Interesting q. I don't believe that info is stored in any system table, I think SQL generates it (only) when the function executes.

To get the column names, you could use "TOP (0)" to get them without actually executing the query, for example:

SELECT TOP (0) * INTO #col_names FROM dbo.DelimitedSplit8K('a,b,c',',');

Then check for the column name(s) in #col_names:

IF EXISTS(SELECT 1 FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb.dbo.#col_names') AND name = 'Item') PRINT 'Column exists.' ELSE PRINT 'Column doesn''t exist.'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Phil's suggestion works for table valued functions, and you can also use dm_exec_describe_first_result_set but you'd have to make the first parameter as a query to the TVF not just the name, so something like these work for me:
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS WHERE TABLE_NAME = 'fun_GetEventCurrent' --or SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.fun_GetEventCurrent(120404)', NULL, 0)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

The column definitions of the returned tables from multi-line and in-line table valued functions are stored in sys.columns. Run the following and see what you get:

select * from sys.sql_modules sm inner join sys.columns col on sm.object_id = col.object_id ;

DesNorton SSC-Insane Points: 24423 More actions · Answer 7

Perhaps this will provide what you are looking for
sys.dm_exec_describe_first_result_set_for_object

How to post data/code on a forum to get the best help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Again, try this:

select object_name(sm.object_id) ObjectName , col.name ColumnName ,ca1.DataType from sys.sql_modules sm inner join sys.columns col on sm.object_id = col.object_id inner join sys.types typ on col.system_type_id = typ.system_type_id and col.user_type_id = typ.user_type_id cross apply (select typ.name + case when typ.name in ('nchar','char','binary') then '(' + cast(col.max_length as varchar) + ')' when typ.name in ('nvarchar','varchar','varbinary') then case when col.max_length = -1 then '(max)' else case when typ.name in ('varchar','varbinary') then '(' + cast(col.max_length as varchar) + ')' else '(' + cast(col.max_length/2 as varchar) + ')' end end when typ.name in ('numeric','decimal') then '(' + cast(col.precision as varchar) + ',' + cast(col.scale as varchar) + ')' when typ.name in ('float','real') then '(' + cast(col.precision as varchar) + ')' when typ.name in ('datetime2','datetimeoffset','time') then '(' + cast(col.scale as varchar) + ')' else '' end as DataType) ca1

;

Matthew Saggers-700210 SSC Enthusiast Points: 183 More actions · Answer 9

All,

Thanks so much, I have taken a bit of all the answer to get all I need, i'm shamed to say when I have seen the response it was a "Doh" moment.

Again tank you

Matt

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Lynn,
There is a little mistake in how your code calculates the length for nchar.
This should be right:
typ.name + case when typ.name like '%char' or typ.name like '%binary' then case when col.max_length = -1 then '(max)' else '(' + cast( col.max_length/ case when typ.name like 'n%' then 2 else 1 end as varchar) + ')' end

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Sergiy - Wednesday, May 3, 2017 7:47 PM
Lynn,
There is a little mistake in how your code calculates the length for nchar.
This should be right:
typ.name + case when typ.name like '%char' or typ.name like '%binary' then case when col.max_length = -1 then '(max)' else '(' + cast( col.max_length/ case when typ.name like 'n%' then 2 else 1 end as varchar) + ')' end

Missed that, thanks.