Custom Function in SQL Statement?

In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.

I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".

What do I need to do to make this work?
All help is greatly appreciated!

Lynnetteasp.net and sql server are two different things...you cannot call a vb/c# function in your sql statement...you can write the function in a UDF in your sql server and call it in your sql statement.|||Thanks for your response. I am brand new to SQL Server, but not so new to asp.net. Any ideas how to modify this vb.net function to a UDF?

Thanks again for all your help!
lds

 Function GetWorkStatus(ByVal sU As Object, ByVal sSG As Object) As Integer
 Dim dtEdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim mydateMonth As Integer = DatePart("m", dtEdate)
 Dim thisDay As String = CStr(DatePart("d", dtEdate))
 Dim sDayOfWeek As String = CStr(DatePart("w", dtEdate))
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim cmd As New SqlCommand("usp_CheckWorkStatus", cnn)
 Try
 With cmd
 .CommandType = CommandType.StoredProcedure
 With .Parameters.Add("@.ForThisDate", SqlDbType.DateTime)
 .Value = dtEdate
 End With
 With .Parameters.Add("@.mydateMonth", SqlDbType.Int)
 .Value = mydateMonth
 End With
 With .Parameters.Add("@.thisDay", SqlDbType.VarChar, 2)
 .Value = thisDay
 End With
 With .Parameters.Add("@.theUnion", SqlDbType.NVarChar, 2)
 .Value = sU
 'Session("sUn") = sU
 End With
 With .Parameters.Add("@.theSG", SqlDbType.NVarChar, 50)
 .Value = sSG
 'Session("strSG") = sSG
 End With
 End With
 cnn.Open()
 Dim da As New SqlDataAdapter(cmd)
 Dim ds As New DataSet
 da.Fill(ds, "SGDetails")
 'no record in FLSAScheduleGroup
 If ds.Tables("SGDetails").Rows.Count = 0 Then
 Select Case sSG
 Case "08"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 7 Then 'Sunday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "09"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 2 Then 'Sunday or Monday
 Return 0
 Else
 Return 1
 End If
 Case "12"
 If sDayOfWeek = 4 OrElse sDayOfWeek = 5 Then 'Wed or Thurs
 Return 0
 Else
 Return 1
 End If
 Case "13"
 If sDayOfWeek = 5 OrElse sDayOfWeek = 6 Then 'Thurs or Friday
 Return 0
 Else
 Return 1
 End If
 Case "14"
 If sDayOfWeek = 6 OrElse sDayOfWeek = 7 Then 'Friday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "31"
 If sDayOfWeek = 1 Or 7 Then
 Return 0
 Else
 Return 1
 End If
 Case Else
 Return 1
 End Select
 ElseIf ds.Tables("SGDetails").Rows.Count = 1 Then 'if there is a row in FLSAScheduleGroup
 Return 0
 End If
 Catch ex As Exception
 lblError.Text = ex.ToString
 Finally
 With cmd.Connection
 If .State = ConnectionState.Open Then
 .Close()
 End If
 End With
 Session("sUn") = Nothing
 Session("strSG") = Nothing
 End Try
 End Function

|||what you have seems to be fine although it can be fine-tuned a little bit more..i dont see where you are calling a function in a sql stmt here..|||I wrote the function for the vb.net code behind, and called it from the <ItemTemplate Text=> part of the datagrid.

I have no idea how to make it work in SQL - don't know how to write a UDF.

Thanks again for all your help. Sorry the code is so spaced out - don't know what the "POST-ing" did to it.

Lynnette|||ok you got me confused when you said you are trying to call a function in codebehind from SQL.

what you have seems to be a regular vb function that queries a database to fill a dataset. so what are you trying to do now ? does this function not work ? do you get any errors and if so at which line ? and what is the xact error message ?|||Thanks so much for sticking with me here. I'm the only one at my office that does .NET, so I'm kind of an island here.

The vb.net Function "GetWorkStatus" in the post above works fine. I get the error, "not a recognized function name" when I try to call it from another function, that is using it in the SQL statement to identify values for the specified field. Here is the calling function:

 Private Sub GetTmpRecs()
 'uses the AppSettings table to generate WHERE clause from the filter values
 Dim dtWdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim wValue As String = Session("svFilterValue")
 Dim dtEdate As DateTime = Now()
 Dim strUser As String = Session("sUser")
 Dim sSQL As String = "INSERT INTO tmpFLSAEmpInfo "
 sSQL = sSQL & " (Emp_Number, PT_ID, Name_full, Division, Department, Job_Dept_Code, Dept_Mgr, "
 sSQL = sSQL & " DeptInfo, Job_Supervisor, Location, Shift, sUnion, sSG, WrkDate, WrkStatus, HrsWorked, Username, AddDate) SELECT "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Emp_Number, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[ID], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Name_Full, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Division, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Department,"
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Job_Dept_Code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Dept_Mgr, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Department + ' - ' + hcsosharedtables.dbo.employee2.Dept_Mgr + ' - ' + hcsosharedtables.dbo.employee2.job_dept_code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Job_Supervisor, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Loc_Name, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Shift, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[Union], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Sched_Group, "
 sSQL = sSQL & "'" & dtWdate & "', "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS WrkStatus, "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS HrsWorked, "
 sSQL = sSQL & "'" & strUser & "', "
 sSQL = sSQL & "'" & dtEdate & "' "
 'sSQL = sSQL & "INTO tmpFLSAEmpInfo "
 sSQL = sSQL & "FROM hcsoSharedTables.dbo.Employee2 " & wValue & ""
 Dim ds As DataSet = New DataSet
 constants.ExecuteSQL(sSQL, constants.SQLConStrFLSA)
 End Sub

|||So I got it right..you were indeed trying to call a vb function into an sql query..I can suggest moving the entire code into a stored proc..move all the logit into it..you can get it all done in one trip.