actual differences btw Stored proc and UDF ?

[HunainHafeez]

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 ?

[JorgeM]

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.

[HunainHafeez]

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 ?

[JorgeM]

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?

[HunainHafeez]

ok jorg

[JorgeM]

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

[HunainHafeez]

my pleasure jorGem :)