Calling Table-Valued SQL Functions From .NET

Question :

Calling Table-Valued SQL Functions From .NET,

Answer :

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).

Read More  Has anyone had any success in unit testing SQL stored procedures?

,

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.

That’s the answer Calling Table-Valued SQL Functions From .NET, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.

Disclaimer :

The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you

Read More  Instrumenting Database Access