Create function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create function

Author

Topic

Maleks
Starting Member

10 Posts

Posted - 2008-11-06 : 06:56:00

Hey there,

A create function script is simple enough but I'm having trouble integrating it into a rerunnable update script. Basically I'd like to check if the function does not exist and if not, then create it.

The following does not seem to work:

IF NOT EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = 'fnGetValue' AND routine_schema = 'dbo' AND routine_type = 'function')
BEGIN
CREATE FUNCTION fnGetValue() RETURNS VARCHAR(255)
WITH SCHEMABINDING
BEGIN
RETURN 'Blah'
END
END

GO

Anyone got any ideas.

Thanks,
Alex

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-06 : 07:23:37

IF NOT EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = 'fnGetValue' AND routine_schema = 'dbo' AND routine_type = 'function')
BEGIN
EXEC('CREATE FUNCTION fnGetValue() RETURNS VARCHAR(255)
WITH SCHEMABINDING
BEGIN
RETURN ''Blah''
END')
END

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 07:25:09

[code]IF EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = 'fnGetValue' AND routine_schema = 'dbo' AND routine_type = 'function')
BEGIN
DROP FUNCTION fnGetValue
END
GO
CREATE FUNCTION fnGetValue() RETURNS VARCHAR(255)
WITH SCHEMABINDING
BEGIN
RETURN 'Blah'
END

GO[/code]

Maleks
Starting Member

10 Posts

Posted - 2008-11-06 : 07:28:36

Thanks guys. Had to go with madhivanan's solution because dropping the function will cause problems.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-06 : 07:39:47

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[fnGetValue]') AND XTYPE IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[fnGetValue]
GO

CREATE FUNCTION fnGetValue() RETURNS VARCHAR(255)
WITH SCHEMABINDING
BEGIN
RETURN 'Blah'
END

================================================

When you realize you've made a mistake, take immediate steps to correct it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources