Calling a UDF

• Matt Hinsley

  SSC Journeyman

  Points: 97

  More actions

  October 26, 2007 at 6:08 am

  #122057

  Hi All

  I'm using SQL Server 2000 SP4.

  I've created a user defined function in my DTS script to convert text to propercase (Title Case). However, when I call the function it runs OK and updates the passed parameter but does not pass the value back to my calling statement.

  What am I doing wrong? Do I need to do this ByRef or similar?

  Help much appreciated, thanks.

  .

  .

  Function Main()

  .

  .

  DTSDestination("Field1") = ProperCase(DTSSource("Field1"))

  Main = DTSTransformStat_OK

  End Function

  Function ProperCase(NewField)

  Dim cVar

  .

  . create cVar as propercase of NewField...

  .

  NewField = cVar

  End Function

• Marshall Smith

  SSCrazy

  Points: 2041

  More actions

  November 2, 2007 at 9:28 am

  #747816

  You need a RETURN clause. Probably specifically, the "NewField = cVar" line should be "RETURN cVar".