How to and where to save self written sql function on sql database?

[omar999]

hi guys

fairly new to sql so need a bit of help. im not sure how to and where to save self written sql function on sql database..

sql function I want to save on db

CREATE FUNCTION MyConvertFunc 
(@InputVals int)
RETURNS int
AS
BEGIN
declare @convertedVal varchar(30)
set @convertedVal =  Cast(@InputVals  * 0.9 as int)
return  @convertedVal 
end

to get started off where would I save this function?

I then would like my front end asp page to call this function within my sql select statement

would the select statement be then something like

select MyConvertFunc, * from tablename WHERE ID ='1'

please advise. thanks in advance.

Omar.

[ck9663]

First, make sure you are in the DB that you need to create the function on. Then create the function using your CREATE FUNCTION code. If there are no error, your function will be created and saved on that DB.

When you open your Enterprise Manager or Management Studio, go to your Database-Programmability-Functions-Scalar-valued and you will see your function.

To see the actual code in a query window, use the sp_helptext command. You can right-click on the function and choose the command you want on the menu that will pop-up.

For more on how you can use it in query, read this.

On how you will use in ASP, that would depend on how you are connecting to the DB and how you are using the function. You may post it in the ASP/.Net forum for more on that one.

Good Luck!!!

~~ CK

[omar999]

hi Ck

thanks for your help. I've managed to successfully create my function on db under Database-Programmability-Functions-Scalar-valued.

I shall post in the asp section for further help on how to call it.

thanks once again
Omar.