Sql and User Defined functions

[lchase]

Hi,
Anyone have an example of using a sql server 2000 user-defined function. I can't seem to find any and am not sure it is allowed.
Can anyone shine some light on this.
Thanks
Len

 

[hmckillop]

SQL function is as follows

CREATE FUNCTION fn_MyFunction
( @parameter1 VARCHAR(10)
)
RETURNS INT
AS
BEGIN
DECLARE @v_MyResult
Select @v_MyResult = Count(*) FROM MyTable Where field1 = @parameter1
RETURN @v_MyResult
END


The code for calling something like this could be
DIM adoConn as AdoDB.Connection
DIM RS as Adodb.Recordset
ADoConn.ConnectionString "blahblah"
SET RS = ADOCOnn.Execute "Select dbo.fn_MyFunction(1)"

 

[lchase]

Thanks
I will give it a try.
Len

 

[lchase]

Hi I tried this and got a type mismatch error.
Thanks Len
CREATE FUNCTION fn_MyFunction
( @Dept_no VARCHAR(10)
)
RETURNS INT
AS
BEGIN
DECLARE @v_MyResult int
Select @v_MyResult = Count(*) FROM employee
Where dept_no = @Dept_no
RETURN @v_MyResult
End
'Set RS =adoConnect.Execute "Select dbo.fn_MyFunction('d1')"
'Set RS = adoConnect
I tried this adoconnect is opened in form load
d1 is a valid value for the column dept_no
when I try it I get type mismatch on the execute line.