Call SQL Function

[sjesweak]

I have the following code which I'm trying to execute but it gives me an error which says

" An insufficient number of arguments were supplied for the procedure or function dbo.Function1"

Public Function test(ByVal connectionString As String)
            Dim sqlConnection1 As New SqlConnection(connectionString)
            sqlConnection1.Open()

            ' Create a command object to call Function1.
            Dim sqlCommand1 As New SqlCommand()
            With sqlCommand1
                .CommandText = "Select dbo.[Function1]()"
                .CommandType = CommandType.Text
                .Connection = sqlConnection1
            End With

            Dim sqlDataReader1 As SqlDataReader
         

            sqlCommand1.Parameters.Add(New SqlClient.SqlParameter("@TEST", SqlDbType.NVarChar, 20))
            sqlCommand1.Parameters("@TEST").Value = "TEST"

            sqlCommand1.Parameters.Add(New SqlClient.SqlParameter("@VALUE", SqlDbType.NVarChar, 20))
            sqlCommand1.Parameters("@VALUE").Direction = ParameterDirection.ReturnValue
            sqlCommand1.Parameters("@VALUE").Value = "<NULL>"

            sqlDataReader1 = sqlCommand1.ExecuteReader
            sqlDataReader1.Read()
            Dim strRetVal As String
            strRetVal = sqlDataReader1.Item(0)

            sqlDataReader1.Close()
        End Function

The Function is created as such

ALTER FUNCTION dbo.Function1(@TEST nvarchar(20))
RETURNS nvarchar(20) 
AS
BEGIN
   declare @out nvarchar(20)
   set @out = 'Hello World'
   RETURN @out
END

 

[JohnH]

Try this like they do in all the SP tutorials out there:

.CommandText = "Function1"
.CommandType = CommandType.StoredProcedure

 

[sjesweak]

Nope. That only gave the error: Can't find stored procedure.

 

[giadich]

.CommandText = "Select dbo.[Function1]('test')"

 

[sjesweak]

Yep, that's what I'm currently having to do at the moment is to hard code the parameters into a string variable and then do string concatenation to place the function paramters. What I'd like to be able to do is to use the sqlparameter variable to apply to the call.

 

[giadich]

not sure if you can do it with function. I have never tried it before.

how come you are not using a stored procedure here? you wouldn't have that issue then.

 

[sjesweak]

I usually write stored procedures that return rows of data and not single values which is the case for my functions. So in my stored procedures I would have to query the dataset I pull it into for the column / row for data.

 

[giadich]

you could use sp and .executescalar method to get a single value. I have to say that is a strange way to determine whether to use sp or function.

 

[sjesweak]

Yep what you just said makes perfect sense and after my quick google search I see what you mean and will be able to use sp's all the time now.

Thanks for your help.

 

[cjard]

I have the following code

If you got the IDE to generate the code for you then:

You wouldnt have to "have" it because the IDE would write it for you
It would be correct, and it wouldnt have errors
It would take less time

 

[sjesweak]

I did the following for those who are wondering.

Public Function executeStoredProc(ByVal connectionString As String, ByVal storedProc As String, ByVal params() As SqlParameter) As Object
Dim sqlCon As New SqlConnection
Dim cmd As New SqlCommand
Dim sqlParam As New SqlParameter
Dim sqlAdap As New SqlDataAdapter
Dim value As Object

sqlCon = New SqlConnection(connectionString)

If sqlCon.State <> Data.ConnectionState.Open Then 'If state is anything other than OPEN
sqlCon.Close() 'Close the connection to eliminate possible error conditions
sqlCon.Open() 'Reopen to run the sqlcommand
End If

cmd = New SqlCommand(storedProc, sqlCon)
cmd.CommandType = CommandType.StoredProcedure

If params IsNot Nothing Then
For Each sqlParam In params
cmd.Parameters.Add(sqlParam)
Next
End If
value = cmd.ExecuteScalar()
sqlCon.Close()

Return value
End Function

As long as I return a value from my sql procedure this one works. I also have one that returns a dataset for when I want to return records.

Thanks again for all the help.