Creating UDF In SQL Server 2005?

I find PLENTY of examples of User Defined functions. However, I
struggle to see how/where to SAVE this function! I can go under the
database, Programmability, Functions, Scalar Functions, and create a
scalar function. However, when I click SAVE I get prompted to save the
.sql file to disk. If I close that (query) tab, then the function is
gone as well.
How do I persist the function in a database? I should note that this is
for SQL Server 2005.<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under
the
> database, Programmability, Functions, Scalar Functions, and create
a
> scalar function. However, when I click SAVE I get prompted to save
the
> .sql file to disk. If I close that (query) tab, then the function
is
> gone as well.
> How do I persist the function in a database? I should note that
this is
> for SQL Server 2005.
>
mikes,
The .sql files are just text files with another extension. Of
course you want to save them as your scripts, but if you want to
check out your stored procedures and functions in the database,
then:
SELECT R1.*
FROM INFORMATION_SCHEMA.ROUTINES AS R1
Sincerely,
Chris O.|||Hi Mike,
When you run the text that you have just created then teh SQL server creates
the object in the database. The text is no longer needed by the SQL Server.
You should however save this text to a file and that file can be stored
anywhere you like
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this is
> for SQL Server 2005.
>|||Why save it when sql will give you the create statement any time you need
it?
William Stacey [MVP]
"GregO" <grego@.community.nospam> wrote in message
news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
> Hi Mike,
> When you run the text that you have just created then teh SQL server
> creates the object in the database. The text is no longer needed by the
> SQL Server. You should however save this text to a file and that file can
> be stored anywhere you like
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
> <mikes@.vmsmailingservices.com> wrote in message
> news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>|||Hello mikes@.vmsmailingservices.com,

> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this
> is for SQL Server 2005.
Rephrasing some of the answers you've gotten thus far, as soon as you execut
e
the CREATE FUNCTION statement, a "complied" version of the function is commi
tted
to the database. There's no need to save the text version of it, since, when
you issued that same CREATE FUNCTION statement, SQL Server also saved that
to the database.
Make sense?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi William
Seriously?
What happens when some deletes it or changes the structure. You need
something to compare it to. There so many reasons for keeping the command
syntax and next to none for not keeping it. When in doubt keep the command.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OVdiJWy7FHA.472@.TK2MSFTNGP15.phx.gbl...
> Why save it when sql will give you the create statement any time you need
> it?
> --
> William Stacey [MVP]
> "GregO" <grego@.community.nospam> wrote in message
> news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
>|||>
> Rephrasing some of the answers you've gotten thus far, as soon as you exec
ute
> the CREATE FUNCTION statement, a "complied" version of the function is com
mitted
> to the database. There's no need to save the text version of it, since, wh
en
> you issued that same CREATE FUNCTION statement, SQL Server also saved that
> to the database.
> Make sense?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
This helps a lot, thanks everyone!
a couple more related questions, based on your responses...
How does the DB engine manage a new function of the same name? Prompted
to override?
How can one determine a list of the functions in a database?
How is (function) debugging done, if the function is internal to the DB
Engine?|||Hello mikes@.vmsmailingservices.com,

> How does the DB engine manage a new function of the same name?
> Prompted to override?
No, unlike other development tools, most of the SQL Server tools *trusts*
the operator to know what he or she is doing. Most shops have a CVS (or simi
lar)
set up for storing scripts for objects. MS is offering one with team system
too. Native integration of that into SQL Server is probably a couple of vers
ions
away I suspect.

> How can one determine a list of the functions in a database?
select * from sys.objects where type_desc like '%_FUNCTION'

> How is (function) debugging done, if the function is internal to the
> DB Engine?
Like any other environment, the execution engine know that it has breakpoint
s
that it yields control to another process. It just so happens that SQL Serve
r's
engine uses a common Visual Studio API for debugging. No magic.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/