How to call SQL Server function?

  • Thread starter Thread starter Antonio Maciel
  • Start date Start date
A

Antonio Maciel

Hi.

I wrote a sql server user defined function that I need to call from vb.net .
This function returns a string that I need to store in a variable. How can I
call this function from my application?

Thanks,

Antonio Maciel
 
sqlCommand cmd = new sqlCommand("select dbo.myFunc()", myConnection);
myConnection.Open();
return cmd.ExecuteScalar();
myConnection.Close();
 
Is that a stored procedure?

You can call stored procedures by setting the CommandType to
"StoredProcedure". The default is "Text", which is what you use for the
standard SQL Select clauses. Then set the CommandText property to the name
of the stored procedure. In this instance you need to create SQLParameter
objects for each of the stored procedure's parameters (if there are any) and
add them the the commands "Parameters" collection.

myConnection.Open();
try
{
SqlCommand cmd = new SqlCommand("myFunc", myConnection);
//^ SqlCommand(string commandText, SqlConnection connection)
cmd.CommandType = CommandType.StoredProcedure;
// you showed no parameters...
return cmd.ExecuteScalar();
}
catch{...handle error...}
finally
{
myConnection.Close();
}

note, your code below would not compile. you would get "code unreachable"
error pointing to your line after the return statement. The above would
work.

To see how to add parameters see the help (watch word wrap):
Visual Studio:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpguide/html/cpconusingstored
procedureswithcommand.htm

msdn website:
http://msdn.microsoft.com/library/d...tml/cpconusingstoredprocedureswithcommand.asp
 
Back
Top