Need Help with UDF's Please

I need a scalar function that I can call from a select statement (with other
criteria) that will dynamically return all the columns (And sometimes their
datatypes with a mode option), from a table or view from any database on tha
t
server. The function needs to work in both SQL 2000 and SQL 2005. I have
listed the function I have tried to create below (using the master database
trying to make a system object but 2005 will not allow me to update system
tables with the UID of 4 - sys in 2005 and system_schema_function in
2000...yes I already know I am a bad developer for trying to cheat). Any
help on this would be greatly appreciated!
Please excuse the formating in advance (was created in VS sql 2005).
Thank You
Jared
/ ****************************************
******************/
/* Name : fn_getcolumns
*/
/* Version : 1.0
*/
/* Author : Jared Kirkpatrick
*/
/* Date : 2006-01-01
*/
/* Description : Retrives column information about an object
*/
/ ****************************************
*****************/
/* Date : Version: Who: Description
*/
/ ****************************************
*****************/
/* 2006-01-01 : 1.0 : JSK : Initial Release.
*/
/ ****************************************
*****************/
USE [master];
GO
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'fn_getcolumns')
BEGIN
DROP FUNCTION [dbo].[fn_getcolumns]
END;
GO
CREATE FUNCTION [dbo].[fn_getcolumns]
(
@.p_DB [NVARCHAR](100)
,@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
,@.v_Result [NVARCHAR](4000)
SELECT @.v_SQL =
'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
+ CHAR(13) + CHAR(10) +
' '' ,['' + [Column_Name] + ''] '' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN @.d_Mode = 1 '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
+ CHAR(13) + CHAR(10) +
' ''['' + UPPER([Data_Type]) + '']'' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''CHAR'''
+ CHAR(13) + CHAR(10) +
' ,''VARCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NVARCHAR'''
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Character_Maximum_Length])
+ '') '' ' + CHAR(13) + CHAR(10) +
' WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''DECIMAL'' '
+ CHAR(13) + CHAR(10) +
' ,''NUMERIC'' '
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
'' + ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') '' '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END + CHAR(13) + CHAR(10) '
+ CHAR(13) + CHAR(10) +
' FROM [INFORMATION_SCHEMA].[COLUMNS] '
+ CHAR(13) + CHAR(10) +
' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
+ CHAR(13) + CHAR(10) +
' AND [Table_Name] = ''' + @.p_Object + ''' '
+ CHAR(13) + CHAR(10) +
' ORDER BY [Ordinal_Position] '
+ CHAR(13) + CHAR(10) +
' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
EXEC sp_executesql @.v_SQL,
N'@.d_Result [NVARCHAR](4000) OUTPUT',
N'@.d_Mode [INT]' ,
@.v_Result OUTPUT ,
@.d_Mode = @.p_Mode
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)Thats an easy one, you can=B4t use execute statements or DML (and
non-temp tables) in functions. Therefore you have to use stored
procedures.
HTH, Jens Suessmeyer.|||The RETURN Statement at the end should be:
RETURN @.v_Result
NOT
RETURN @.v_SQL
Excuse me I was trobleshooting.
"kirk1880" wrote:

> I need a scalar function that I can call from a select statement (with oth
er
> criteria) that will dynamically return all the columns (And sometimes thei
r
> datatypes with a mode option), from a table or view from any database on t
hat
> server. The function needs to work in both SQL 2000 and SQL 2005. I have
> listed the function I have tried to create below (using the master databas
e
> trying to make a system object but 2005 will not allow me to update system
> tables with the UID of 4 - sys in 2005 and system_schema_function in
> 2000...yes I already know I am a bad developer for trying to cheat). Any
> help on this would be greatly appreciated!
> Please excuse the formating in advance (was created in VS sql 2005).
> Thank You
> Jared
> / ****************************************
******************/
> /* Name : fn_getcolumns
> */
> /* Version : 1.0
> */
> /* Author : Jared Kirkpatrick
> */
> /* Date : 2006-01-01
> */
> /* Description : Retrives column information about an object
*/
> / ****************************************
*****************/
> /* Date : Version: Who: Description
> */
> / ****************************************
*****************/
> /* 2006-01-01 : 1.0 : JSK : Initial Release.
> */
> / ****************************************
*****************/
> USE [master];
> GO
> IF EXISTS(SELECT 1
> FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
> WHERE [SPECIFIC_SCHEMA] = 'dbo'
> AND [SPECIFIC_NAME] = 'fn_getcolumns')
> BEGIN
> DROP FUNCTION [dbo].[fn_getcolumns]
> END;
> GO
> CREATE FUNCTION [dbo].[fn_getcolumns]
> (
> @.p_DB [NVARCHAR](100)
> ,@.p_Owner [NVARCHAR](100)
> ,@.p_Object [NVARCHAR](100)
> ,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
> )
> RETURNS [NVARCHAR](4000)
> WITH ENCRYPTION
> AS
> BEGIN -- Begin function
> /* Declare Variables */
> DECLARE @.v_SQL [NVARCHAR](4000)
> ,@.v_Result [NVARCHAR](4000)
> SELECT @.v_SQL =
> 'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
> + CHAR(13) + CHAR(10) +
> ' '' ,['' + [Column_Name] + ''] '' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN @.d_Mode = 1 '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
> + CHAR(13) + CHAR(10) +
> ' ''['' + UPPER([Data_Type]) + '']'' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''CHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''VARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NVARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Character_Maximum_Length
])
> + '') '' ' + CHAR(13) + CHAR(10) +
> ' WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''DECIMAL'' '
> + CHAR(13) + CHAR(10) +
> ' ,''NUMERIC'' '
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
> '' + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') ''
'
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END '
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END + CHAR(13) + CHAR(10) '
> + CHAR(13) + CHAR(10) +
> ' FROM [INFORMATION_SCHEMA].[COLUMNS] '
> + CHAR(13) + CHAR(10) +
> ' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
> + CHAR(13) + CHAR(10) +
> ' AND [Table_Name] = ''' + @.p_Object + ''' '
> + CHAR(13) + CHAR(10) +
> ' ORDER BY [Ordinal_Position] '
> + CHAR(13) + CHAR(10) +
> ' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
> EXEC sp_executesql @.v_SQL,
> N'@.d_Result [NVARCHAR](4000) OUTPUT',
> N'@.d_Mode [INT]' ,
> @.v_Result OUTPUT ,
> @.d_Mode = @.p_Mode
> RETURN @.v_SQL;
> END; -- Begin function
> GO
> --SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)|||Jens,
First: I really appreciate the feedback
Second: I understand that you cannot execute statements in UDF's which is
why I am looking for help. A sp will not help me in this situation because
I
need the information within another dynamic statement, and you cannot call a
n
sp within inline SQL. I could use the code that makes this UDF and place it
in every instance I need within the sp I call but we are talking about 87
diffrent calls to date and more coming around the corner. I would really
like to find a solution that will allow me to somehow do the following by
making a system function or I will need to start making an xp that can be
called from a UDF (Of cource that calls for me to register the xp and have
code outside of T-SQL that makes things harder to manage).
"Jens" wrote:

> Thats an easy one, you can′t use execute statements or DML (and
> non-temp tables) in functions. Therefore you have to use stored
> procedures.
> HTH, Jens Suessmeyer.
>|||> why I am looking for help. A sp will not help me in this situation
> because I
> need the information within another dynamic statement, and you cannot call
> an
> sp within inline SQL.
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dynamic_sql.html|||kirk1880 (kirk1880@.discussions.microsoft.com) writes:
> Second: I understand that you cannot execute statements in UDF's which
> is why I am looking for help. A sp will not help me in this situation
> because I need the information within another dynamic statement, and you
> cannot call an sp within inline SQL. I could use the code that makes
> this UDF and place it in every instance I need within the sp I call but
> we are talking about 87 diffrent calls to date and more coming around
> the corner. I would really like to find a solution that will allow me
> to somehow do the following by making a system function or I will need
> to start making an xp that can be called from a UDF (Of cource that
> calls for me to register the xp and have code outside of T-SQL that
> makes things harder to manage).
There is a way: you can call extended stored procedures from UDFs. Thus
you can call xp_cmdshell which in it's turns invokes to OSQL and
runs the query. The query would have to insert the data into a table,
and then you would pick up the data from the table in the UDF.
Now, the design of UDFs is that they are not supposed to change database
state, which is why all those restrictions with them are there. This is
a loophole, and there is not really any guarantees that it will work
as expected - if you get something work now, it could break SQL 2005
or in the next service pack.
Thus, I strongly discourage you from trying this. It's a fun thing to
do at parties, but is unsuitable for production code.
What you really should do is to go back to your original business
problem and analyse why you ended up with all these 87 calls. The best
I can recommend at this point is to bounce data over a temp table, or
a process-keyed table, see http://www.sommarskog.se/share_data.html
for details. But I suspect that you need a complete redesign.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also not preferable would be to use OPENQUERY which is able to execute
a Storedprocedure as a source of a result set, but this has many
mantrap and is not suggestable.
HTH, Jens Suessmeyer.|||Thank you everyone that replied to this post. The solution is actually
simplified (I was trying to make it way to hard). I hold a master schema
table of all the user databases that are will be using dynamic table/view
creates and dynamic insert/update/delete. So my original code/function that
is not dynamic (listed below) works great but only in the database that the
original objects reside...so the solution is to use OSQL and have a system
wrapper that compiles the same function in every database that is flagged,
that way the dynamic procedure that is already in whatever database that is
flagged will have access to return the results I need. Kinda of a kluge to
have multiple objects with diffrent id's in each database, but it works.
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'FSU_GetColumns')
BEGIN
DROP FUNCTION [dbo].[FSU_GetColumns]
END;
GO
CREATE FUNCTION [dbo].[FSU_GetColumns]
(
@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
SELECT @.v_SQL = ISNULL(@.v_SQL, '') + ' ' +
' ,[' + [Column_Name] + '] ' +
CASE WHEN @.p_Mode = 1
THEN REPLICATE(' ', (25 - LEN([Column_Name]))) +
'[' + UPPER([Data_Type]) + ']' +
CASE WHEN [Data_Type] IN (
'CHAR'
,'VARCHAR'
,'NCHAR'
,'NVARCHAR'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Character_Maximum_Length]) +
') '
WHEN [Data_Type] IN (
'DECIMAL'
,'NUMERIC'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Numeric_Precision]) + ', ' +
CONVERT(NVARCHAR(10), [Numeric_Scale]) + ') '
ELSE ''
END
ELSE ''
END + CHAR(13) + CHAR(10)
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [Table_Schema] = @.p_Owner
AND [Table_Name] = @.p_Object
ORDER BY [Ordinal_Position]
SELECT @.v_SQL = STUFF(@.v_SQL, 1, 3, ' ');
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[FSU_GetColumns]('dbo', 'TU_Registry', 1);