May 11, 2015 at 2:18 pm
Regular functions store the return value as parameter zero in sys.parameters but I can't find the TVF table definition for the following sample function. Is the function's return table definition accessible to us in a system table?
CREATE FUNCTION dbo.StringParse
(
@STR varchar(max),
@delimiter varchar(20),
@emptytonull bit
)
returns @tokens TABLE (rownbr int PRIMARY KEY NOT NULL, token varchar(max) NULL)
AS
BEGIN
/*
split a string INTO tokens AND RETURN them IN a TABLE.
empty string returns one NULL row
a string WITH only a delimiter returns two NULL rows, one before it AND one after it.
a delimiter at the beginning means a NULL value appears before it.
two delimiters IN a row implies a NULL value BETWEEN them.
a delimiter at the END OF string implies a NULL value after it.
*/
DECLARE
@delimiterlength int,
@currptr int,
@nextptr int,
@token varchar(max),
@rownbr int
;
SET @delimiterlength = datalength(@delimiter);
SET @rownbr = 0;
SET @currptr = 1 - @delimiterlength;
SET @nextptr = charindex(@delimiter, @STR, @currptr+@delimiterlength);
--SELECT @currptr, @nextptr, @delimiterlength
WHILE @nextptr > 0
BEGIN
SET @rownbr = @rownbr + 1;
SET @token = substring(@str, @currptr+@delimiterlength, @nextptr - (@currptr+@delimiterlength));
IF len(@token) = 0
BEGIN
IF @emptytonull=1
SET @token = NULL
ELSE
SET @token = ''
END
INSERT INTO @tokens VALUES (@rownbr, @token);
SET @currptr=@nextptr;
SET @nextptr=charindex(@delimiter, @STR, @currptr+@delimiterlength);
END
-- last row
SET @rownbr = @rownbr + 1;
SET @token = substring(@str, @currptr + @delimiterlength, datalength(@str) - @currptr + @delimiterlength);
IF len(@token) = 0
BEGIN
IF @emptytonull=1
SET @token = NULL
ELSE
SET @token = ''
END
INSERT INTO @tokens VALUES (@rownbr, @token);
RETURN;
END
GO
SELECT *
FROM sys.objects o
JOIN sys.parameters p on o.object_id = p.object_id
WHERE o.name = 'StringParse'
Relevant responses answering my actual question will be greatly appreciated.
Thanks in advance.
May 11, 2015 at 2:33 pm
Bill Talada (5/11/2015)
Regular functions store the return value as parameter zero in sys.parameters but I can't find the TVF table definition for the following sample function. Is the function's return table definition accessible to us in a system table?...
Quick thought before you go further down this road, have a look at Jeff Moden's Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
😎
May 11, 2015 at 2:51 pm
Eirikur Eiriksson (5/11/2015)
Bill Talada (5/11/2015)
Regular functions store the return value as parameter zero in sys.parameters but I can't find the TVF table definition for the following sample function. Is the function's return table definition accessible to us in a system table?...Quick thought before you go further down this road, have a look at Jeff Moden's Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
😎
This is exactly what I mean by "not a relevant reply".
May 11, 2015 at 3:22 pm
Couldn't find anything to support this, but it probably exists in tempdb while the function is used since it is a table variable returned by the function.
May be something worth investigating when I have some spare time.
May 11, 2015 at 3:28 pm
Bill Talada (5/11/2015)
Eirikur Eiriksson (5/11/2015)
Bill Talada (5/11/2015)
Regular functions store the return value as parameter zero in sys.parameters but I can't find the TVF table definition for the following sample function. Is the function's return table definition accessible to us in a system table?...Quick thought before you go further down this road, have a look at Jeff Moden's Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
😎
This is exactly what I mean by "not a relevant reply".
It was driven by the sample function, not the question;-)
To answer the question, look up "describe first result set".
😎
May 11, 2015 at 3:34 pm
Bill Talada (5/11/2015)
Eirikur Eiriksson (5/11/2015)
Bill Talada (5/11/2015)
Regular functions store the return value as parameter zero in sys.parameters but I can't find the TVF table definition for the following sample function. Is the function's return table definition accessible to us in a system table?...Quick thought before you go further down this road, have a look at Jeff Moden's Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
😎
This is exactly what I mean by "not a relevant reply".
Calm down, Bill. He's just trying to help.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2015 at 3:45 pm
Try this, Bill. The sys.parameters thing isn't for the return of TVFs. sys.columns is.
SELECT OBJECT_NAME(object_id),*
FROM sys.columns
WHERE object_id = OBJECT_ID('nameofyourfunctionhere')
;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2015 at 4:31 pm
Jeff Moden (5/11/2015)
Try this, Bill. The sys.parameters thing isn't for the return of TVFs. sys.columns is.
SELECT OBJECT_NAME(object_id),*FROM sys.columns
WHERE object_id = OBJECT_ID('nameofyourfunctionhere')
;
Thanks Jeff. Learned something new today. Obviously my boogle-fu failed to find anything on this.
May 11, 2015 at 6:17 pm
Lynn Pettis (5/11/2015)
Jeff Moden (5/11/2015)
Try this, Bill. The sys.parameters thing isn't for the return of TVFs. sys.columns is.
SELECT OBJECT_NAME(object_id),*FROM sys.columns
WHERE object_id = OBJECT_ID('nameofyourfunctionhere')
;
Thanks Jeff. Learned something new today. Obviously my boogle-fu failed to find anything on this.
Thanks, Lynn. Now I'm curious as to why someone would need to know this.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2015 at 7:26 am
Thanks everyone, the sys.columns does hold the TVF returned columns; not sure how I missed that except I was also working with TVPs at the same time which don't make use of sys.columns for meta data.
My standard for the team is to name parameters after the columns they affect; that way I can run some queries to make sure parameter data types and lengths match column data types. Also I'm working on some new code generators and documenters that will handle tables returned from functions.
May 12, 2015 at 7:47 am
Bill Talada (5/12/2015)
Thanks everyone, the sys.columns does hold the TVF returned columns; not sure how I missed that except I was also working with TVPs at the same time which don't make use of sys.columns for meta data.My standard for the team is to name parameters after the columns they affect; that way I can run some queries to make sure parameter data types and lengths match column data types. Also I'm working on some new code generators and documenters that will handle tables returned from functions.
Very cool and great idea on the documenters. Thanks for the info, Bill.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply