|
|
SQL Server T-SQL User-Defined Function of the Week
Volume 1 Number 33 July 1, 2003
Getting the Columns Returned by a UDF
Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
A reader recently wrote to ask if it was possible for a UDF
to return a table of the column information for every column in
a SELECT statement. For example, supposing the UDF is named
udf_AllDataTypesFrom, you might request the columns from a
query on Pubs..Authors with this statement:
use pubs
go
select Column, Datatype
from udf_AllDataTypesFrom ('select * from authors')
go
and expect the results:
Column Data Type
--------------- ------------------------------------
au_id int
au_lname varchar(40)
au_fname varchar(20)
Phone char(12)
Address varchar (40)
City varchar (20)
State char(2)
Zip char(5)
Contract bit
I can think of a way to do this in a stored procedure but not
in a UDF. That's because the approaches that come to mind
involve creating objects in tempdb. UDFs are not allowed to
create any objects, much less objects in tempdb. All the
approaches that I've come up with also involves dynamic SQL and
UDFs can't execute dynamic SQL.
So I'm not going to be able to handle the general case that
the reader was interested. At least not from a UDF. However,
as I read further it became apparent that what he really needed
was an easier way to write the table definition when converting
inline UDFs to multistatement UDFs.
That can be handled pretty easily by a UDF. Instead of getting
information from the columns returned by a SELECT statement
the information can be retrieved from an INFORMATION_SCHEMA view
named ROUTINE_COLUMNS.
This issues's UDF returns the text that you'd use to create the
RETURNS TABLE declaration at the top of a multistatement UDF. It
depends on udf_SQL_DataTypeString, which you'll find in
Issue #18. Here's the CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_Func_TableCols (
@Function_Name as nvarchar(778) = NULL -- NULL for All
-- or LIKE pattern on the name of the function.
) RETURNS TABLE
-- No SCHMABINDING due to use of INFORMATION_SCHEMA
/*
* Returns a single column TABLE. Each row is the declaration
* of a column in the output of a UDF. It works for both Inline
* and Multistatement UDFs but is most useful as an aid to
* converting an Inline UDF to a Multistatement UDF.
*
* Example:
SELECT * FROM udf_Func_TableCols ('udf_Func_TableCols') -- me
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as the T-SQL UDF of the Week Vol 1 #34
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN
SELECT TOP 100 PERCENT WITH TIES
' '
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' ' ELSE ',' END
+ ' ['
+ COLUMN_NAME
+ '] '
+ dbo.udf_SQL_DataTypeString (DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION, NUMERIC_SCALE)
+ CASE WHEN 1=COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
, COLUMN_NAME, 'AllowsNull')
THEN ' NULL '
ELSE ''
END
+ ' -- ' as ColumnDeclaration
FROM
INFORMATION_SCHEMA.ROUTINE_COLUMNS c
WHERE TABLE_NAME = @Function_Name
ORDER BY ORDINAL_POSITION
GO
GRANT SELECT ON dbo.udf_Func_TableCols to [PUBLIC]
go
\-------Stop copying above this line ---------------------------/
For an example, lets create the following sample UDF. Although
it selects data from pubs, it can be created in any database.
I created it in my scratch database. Here's the CREATE FUNCTION
script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_Title_AuthorsTAB (
@Title_ID char(6) -- title ID from Pubs Database
) RETURNS TABLE -- List of authors
/*
* Returns a table of information about all authors of the title.
*
* Example:
Select * FROM dbo.udf_Title_AuthorsTAB('BU1032')
****************************************************************/
AS RETURN
SELECT TOP 100 PERCENT WITH TIES
a.au_id
, a.au_lname
, a.au_fname
FROM pubs..Titles T
INNER JOIN pubs..Titleauthor TA
on T.title_id = TA.Title_id
INNER join pubs..authors a
on TA.au_id = A.AU_ID
WHERE T.Title_id = @Title_ID
ORDER BY t.title_id, au_lname
, au_fname
GO
GRANT SELECT ON [dbo].[udf_Title_AuthorsTAB] to PUBLIC
GO
\-------Stop copying above this line ---------------------------/
Now, let's get the column declarations for the table returned
by udf_Title_AuthorsTAB with this script:
/------- Start copying below this line -------------------------\
SELECT * from udf_Func_TableCols ('udf_title_authorsTAB')
go
\-------Stop copying above this line ---------------------------/
(Results)
ColumnDeclaration
-----------------------------------------------------------------
[au_id] varchar(11) --
, [au_lname] varchar(40) --
, [au_fname] varchar(20) --
If you bracket this output with the RETURNS TABLE statement and
a closing parenthesis you have the table declaration that can
be used for creating a multistatement UDF. It would look like:
RETURNS @BookAuthors TABLE (
[au_id] varchar(11) --
, [au_lname] varchar(40) --
, [au_fname] varchar(20) --
)
Now you can go on to write the rest of the UDF.
+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
