Menu
Scalar-valued functions can be called from .NET as follows:
SqlCommand cmd = new SqlCommand("testFunction", sqlConn); //testFunction is scalar cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("retVal", SqlDbType.Int); cmd.Parameters["retVal"].Direction = ParameterDirection.ReturnValue; cmd.ExecuteScalar(); int aFunctionResult = (int)cmd.Parameters["retVal"].Value; I also know that table-valued functions can be called in a similar fashion, for example:
String query = "select * from testFunction(param1,...)"; //testFunction is table-valued SqlCommand cmd = new SqlCommand(query, sqlConn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(tbl); My question is, can table-valued functions be called as stored procedures, like scalar-valued functions can? (e.g., replicate my first code snippet with a table-valued function being called and getting the returned table through a ReturnValue parameter).
517
The simple definition of the table-valued function (TVF) can be made such like that; a user-defined function that returns a table data type and also it can accept parameters. TVFs can be used after the FROM clause in the SELECT statements so that we can use them just like a table in the queries.
We can execute a function in C# using a SqlCommand object and passing a SQL defined function in a SELECT SQL query.
Step 1 − Select the Console Application from the middle pane and enter TableValuedFunctionDemo in the name field. Step 2 − In Server explorer right-click on your database. Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.
No because you need to select them. However you can create a stored proc wrapper, which may defeat the point of having a table function.
189
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
