actual differecne between SQL Server's STORE PROCEDURES and USER DEFINED FUNCTIONS ? i looked up these in google but couldn't satisfy me. Differences ? better to use ? and why does UDF can only implement SELECT statements not else , y ?

Edited by Reverend Jim: typo

Votes + Comments
nice thread, its been very useful for me.
3 Years
Discussion Span
Last Post by HunainHafeez

Stored procedures and functions seem to be similar and they can be but they are generally used for different reasons. purposes. You can use user defined functions to process input and return output, very similar to a typical program. Stored procedures are created when you need more processing. Updating records, executing more complex processes, for example. A function is a bit more limited.

Also, stored procedures allow you to interact with external components say from asp.net. You couldn't call a function in that scenario.


great , so i mean that i can't 'call and get' value from SQL UD function, in asp.net ? like i do with SP ?is it ?


Hmm. Before I say that it can't be done, I'm going to double check. I've been under the assumption that you cannot but why not?


Ok, so I'm very greatful that you created this thread. I've been misinformed and was not completely well versed in accessing functions from asp.net.

It it supported and easy to do. I created a simple scalar function that accepted one paramter. Called the function from my code-behind and had no problems at all.

int ID = some_value
string cstr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
SqlConnection conn = new SqlConnection(cstr);
string cmdstring = "SELECT dbo.ufnGetQty(@ID)";
SqlCommand cmd = new SqlCommand(cmdstring, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

I've typically used functions in sql when needing to compute values where i take advantage of its simplicity and reuse. I use stored procedures more like sql "programs", typically when needing to schedule user defined jobs, and in the application layer of systems.

I'm sure you came accross this article, but i thought it would be good to reference here, just to keep handy: User-Defined Fuctions

Edited by JorgeM

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.