Conditional Function Creation - 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 2000 Forums

Transact-SQL (2000)

Conditional Function Creation

Author

Topic

Dev Null
Starting Member

39 Posts

Posted - 2004-08-25 : 04:00:41

I want to create a function, but only if it doesn't already exist. Reduced to its simplest form, that doesn't seem very hard at all; after all, I know how to check if it exists:

1> IF (NOT EXISTS (SELECT id FROM sysobjects WHERE name = 'funct'))
2> print 'Function does not exist.'
3> go
Function does not exist.

...and I know how to create the function:

1> create function funct ()
2> returns real
3> begin
4> return 1.1
5> end
6> go

...which works fine on its own. But combining the two working bits together generates a couple of seemingly irrelevant errors:

1> IF (NOT EXISTS (SELECT id FROM sysobjects WHERE name = 'funct'))
2> create function funct ()
3> returns real
4> begin
5> return 1.1
6> end
7> go
Msg 156, Level 15, State 1, Server <server>, Line 2
Incorrect syntax near the keyword 'function'.
Msg 178, Level 15, State 1, Server <server>, Line 6
A RETURN statement with a return value cannot be used in this context.

I'm confused; why can't I create my function inside an IF block?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-25 : 04:38:18

Your statements would have to be in a begin / end block to havev a chance but create has to be the first statement of a batch so it wouldn't work anyway.
IF (NOT EXISTS (SELECT id FROM sysobjects WHERE name = 'funct'))
drop function funct
go
create function funct ()
returns real
begin
return 1.1
end
go

or
IF (NOT EXISTS (SELECT id FROM sysobjects WHERE name = 'funct'))
exec ('create function funct ()
returns real
begin
return 1.1
end')

But if the second you should consider (or reconsider) why you want to do this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-25 : 07:25:07

You could use EM to script the function.
If the correct checkboxes are checked then EM will create the drop / create code for you to study.

Otherwise I confirm nr's post.

If you are doing this from code, issue 2 commands against the database.
1. Drop existing if it exists
2. Create function

/rockmoose

Dev Null
Starting Member

39 Posts

Posted - 2004-08-25 : 20:09:37

Thanks guys; I had tried the begin end block - which, as you suggest, doesn't work - but hadn't thought of using an exec.

The only reason besides style preferrence I had for not dropping it first and re-creating it was to bypass the relatively small performance hit of dropping and building a dozen or so functions once an hour. If a function exists with the right name then I can be certain that it does the right thing unless a malicious user with dbo rights is deliberately trying to screw me, in which case I'm screwed anyways; they could just drop my data tables and save the stuffing around...

- rob.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-25 : 21:02:10

Re-creating functions once per hour??
Methinks that's the root of this problem. Why are you doing that?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Dev Null
Starting Member

39 Posts

Posted - 2004-08-26 : 03:31:59

Well I'm not - thats why I needed the if-not-exist-create check. I need to create them dynamically, because I can't tell beforehand what exactly I'll need, but they're fixed enough that the lineup will only change very occasionally, which is why I don't want to destroy them every time.

Basically it comes down to a bodgy workaround for the fact that I can't write my own aggregate functions (or at least I don't know how, and noone on here had any suggestions) and also that TSQL won't let me use dynamic SQL in a function (*grrr*). Best solution I found was to automatically create a separate modified mean function for every column of every table the user tells me they want a modified mean of... but they can always add more columns to that list later. Its gross, but it works.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-26 : 11:14:19

>> because I can't tell beforehand what exactly I'll need
Well there's your problem.

If you want to do it then hold the functions in files, check for existence and use osql to add them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources