WayneS (3/27/2014)
Well, hello there.Luis - thanks for linking to my article. However, I'm afraid that it doesn't do much for helping to learn how to remove cursors. For that task, my favorite articles can be found here[/url] - it should be obvious which two articles I'm referring to.
priestxandar - This latest cursor-ridden function just makes we want to say "OMG". For several reasons.
1. It's a scalar function. These don't scale well (nor do multi-statement table-valued functions). Please read this blog post[/url] for more information. If you can convert these scalar functions to inline table-valued functions, your server will be able to take a deep sigh of relief.
2. We don't have the definitions for the views that are referenced. Please script out and post the DDL for the views vw_CallForTender, [vw_Bid_Criterias] and any other views referenced in this function.
3. How are these functions being used? If you can include those queries, I bet we can make the entire process even better!
4. Luis and I both have links in our signatures for how to post data to get better help. It really does make a difference. Please do this. Or...
5. Consider hiring someone (like myself) to look at your system and re-write this code. It seems like you are in over your head, and this may well be the better method.
1. First to say thanks you are trying to help me, this articles help me to understand lot of things. If i understood good, this function can not be rewritten to inline table valued because is using parameters and can not be done in single select statement.
2. As i mention before its over complicated to include test data, those views have nested views and again nested views, also and lot of joins ( very very bad design). ( i could try to script out the views, and the nested views 🙂 also and the stored procedure if that helps but i doubt)
3. This function is called in stored procedure in select statement, this function should get back SUM based on some criteria (if you look on the joins in function you will notice that).
This is the part where i'm confused most.
4. I'm new to this forum and also to sql server, i'm starting my career now, and i'm trying to learn as much as possible.
5. I'm not in position to hire anybody, i need to learn as much as i can and apply that knowledge.
it will be good if someone could make some general query/idea how could be rewritten based on the info we have right now, i will try to script out those views and proc in monday.
thanks again for being nice and helpful.