User defined function design issues.

Here is a little something I put together to show me design issues in user defined functions.

User defined functions should be used to transform data or implement business logic and should rarely contain select statements. User Defined Tables should be used for logic requiring select statements and should be written using set based logic.

-- Functions that don't return tables but contain select statements.

SELECT DISTINCT

CalledFunction =OBJECT_NAME(OBJECT_ID)

, CallingFunction = Functions2.FunctionName

, Functions2.PartFound

, CallingFunctionDefinition = FunctionDefinition

FROM sys.sql_modules

-- Functions that call this function

CROSS APPLY[dbo].[udt_sql_modules](OBJECT_NAME(OBJECT_ID))Functions2

WHERE definition like '%CREATE FUNCTION%'

AND not definition like '%RETURNS%TABLE%'

AND definition like '%SELECT%FROM%'

/*

CREATE FUNCTION [dbo].[udt_sql_modules](

@FunctionName sysname

)

RETURNS @t TABLE

(

FunctionName sysname

, FunctionDefinition varchar(max)

, PartFound varchar(max)

, PRIMARY KEY CLUSTERED (FunctionName )

)

BEGIN

INSERT INTO @t(

FunctionName

, FunctionDefinition

, PartFound

)

SELECT DISTINCT

FunctionName = OBJECT_NAME(OBJECT_ID)

, definition

, PartFound = SUBSTRING(definition, PATINDEX('%' + @FunctionName + '%',definition) - 50, 100)

FROM sys.sql_modules

WHERE definition like '%' + @FunctionName + '%'

AND OBJECT_NAME(OBJECT_ID) <> @FunctionName

RETURN

/*

SELECT * FROM [dbo].[udt_sql_modules]('')

*/

END

GO

*/