[dba-SQLServer] SQL Server 2000 - User Defined Functions

In the UDFs that I use, I call them like I would in Access.

SELECT udfConcatAddress(5)
FROM tblNameAddress
WHERE NameID = 5

My UDF would have a SELECT in it also where it would SELECT the data 
from the table based on the NameID that I pass in as the 
parameter.  Then it would assemble the string and pass it back as the 
result of the function call.

I use a similar thing to de-normalize data, like hobbies, to display 
it on web forms in query results.

Robert

At 12:00 PM 10/13/2005, you wrote:
>Date: Thu, 13 Oct 2005 09:19:52 -0400
>From: "Arthur Fuller" <artful at rogers.com>
>Subject: Re: [dba-SQLServer] SQL Server 2000 - User Defined Functions
>         -       CallingFrom Visual Basic 6.0
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <200510131319.j9DDJrT18726 at databaseadvisors.com>
>Content-Type: text/plain;       charset="us-ascii"
>
>If I understand this correctly, then you are trying to execute your UDF as
>if it were a sproc. That won`t work.
>Try some variant instead...
>SELECT * FROM myUDF WHERE parm1 = etc.
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
>Hartland
>Sent: October 13, 2005 8:31 AM
>To: dba-sqlserver at databaseadvisors.com
>Subject: [dba-SQLServer] SQL Server 2000 - User Defined Functions -
>CallingFrom Visual Basic 6.0
>
>To all,
>
>I have just created my very first UDF in SQL Server 2000 basically I
>want to use it to return a print address to a string variable in Visual
>Basic 6.0, my function is as below:
>
>CREATE FUNCTION [gng_create_PrintAddress]
>         (
>                 @strLine1       [nvarchar](255),
>                 @strLine2       [nvarchar](255),
>                 @strLine3       [nvarchar](255),
>                 @strLine4       [nvarchar](255),
>                 @strLine5       [nvarchar](255),
>                 @strLine6       [nvarchar](255),
>                 @strPC          [nvarchar](255)
>
>)
>RETURNS
>                 [nvarchar](2000)
>AS
>BEGIN
>         RETURN ISNULL(@strLine1+CHAR(13),'')    +
>                    ISNULL(@strLine2+CHAR(13),'')        +
>                    ISNULL(@strLine3+CHAR(13),'')        +
>                    ISNULL(@strLine4+CHAR(13),'')        +
>                    ISNULL(@strLine5+CHAR(13),'')        +
>                    ISNULL(@strLine6+CHAR(13),'')        +
>                    ISNULL(@strPC+CHAR(13),'')
>END
>
>In VB6 I have a data environment with a SQLConn connection, and if I
>want to execute a stored procedure to return a recordset I do something
>like the following:
>
>Dim rsData As ADODB.Recordset
>Set rsData = DEGNG.SQLConn.Execute("[SP Name]") and give it the
>parameters if required.....
>
>So I thought I could have something like below:
>
>Dim strAdd As String
>strAdd = DEGNG.SQLConn.Execute("gng_create_PrintAddress '" & Line1 & "',
>'" & Line2 & "','" & Line3 & "', '" & Line4 & "', '" & Line5 & "', '" &
>Line6 & "', '" & PCode & "'")
>
>And then strAdd would contain the print address, but it give me an error
>saying: Type Mismatch.
>can anyone point me in the right direction here ?
>
>Thanks in advance for any help on this, as I would really like to start
>learning more about UDF's and using them instead of modules in VB6
>
>
>
>
>
>
>
>PAUL HARTLAND
>Database Designer/Programmer
>paul.hartland at orridge.co.uk
>DDI - 01922 472031
>Mobile - 07730 523179