November 4, 2016 at 3:50 pm
I have a productCase table that we created cases for some products. Then there are some other tables related with this productCase table such as productCaseNotes, ProductCaseNotifications etc.
I would like to check if any thing entered into the other tables related with the productCases.
So I have a query that joined with productCase that that check certain fields are entered or not.
But since this is used in many places of our code, I would like to make it as a function.
Called something like ProductCaseWithInput. ( not good name, but just use temporarily)
My question what is the best way, should I create a table function with only two fields productcaseID and WithInputFlag (which is 1 means user entered data, 0 means no data entered)
By using table valued function, I can join the table later on productCaseID.
Or should I use a scalar function with a parameter- productCaseID? and just return true or false for WithinputFlag?
Which one will be more effective, table valued function or scalar valued function in this case?
Thanks,
November 4, 2016 at 6:51 pm
Why do you need anything more than a straight-up SELECT statement?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2016 at 7:28 pm
Thanks. Because it will be used in several places , so other developer request to create a function for reuse
November 5, 2016 at 4:34 am
Assuming we're talking about an inline table function, that's the approach I would use. If you're looking at the multi-statement table valued function, I would caution against it.
Be careful. You're going for code reuse. SQL Server and T-SQL are not great at that type of behavior. Functions joined to functions or functions calling functions can lead to lots of issues with performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 5, 2016 at 2:47 pm
sqlfriends (11/4/2016)
Thanks. Because it will be used in several places , so other developer request to create a function for reuse
Often in SQL, 'reusable code' = 'slow code'
Have you considered a view?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2016 at 11:10 pm
sqlfriends (11/4/2016)
I have a productCase table that we created cases for some products. Then there are some other tables related with this productCase table such as productCaseNotes, ProductCaseNotifications etc.I would like to check if any thing entered into the other tables related with the productCases.
So I have a query that joined with productCase that that check certain fields are entered or not.
Have you considered alternatives such as simplifying the check? One option when checking if multiple columns in the same table have values is to multiply the data length of those columns in a calculated columns, if any of those is empty then the results will be 0.
😎
November 7, 2016 at 9:59 am
Grant Fritchey (11/5/2016)
Assuming we're talking about an inline table function, that's the approach I would use. .
Yes it is a inline table valued function, not multiple statement function
November 7, 2016 at 10:01 am
GilaMonster (11/5/2016)
sqlfriends (11/4/2016)
Thanks. Because it will be used in several places , so other developer request to create a function for reuseOften in SQL, 'reusable code' = 'slow code'
Have you considered a view?
Thanks. The view seems OK too.
This was raised by front end .net developers. They always like to use functions I guess in .net code for reuse, so they think the same way and ask SQL developer to do the same thing.
November 7, 2016 at 10:02 am
Have you considered alternatives such as simplifying the check? One option when checking if multiple columns in the same table have values is to multiply the data length of those columns in a calculated columns, if any of those is empty then the results will be 0.
😎
We have to check if user entered data in the subsequent child tables of the parent table, there are about 5 child tables.
November 7, 2016 at 10:07 am
sqlfriends (11/7/2016)
front end .net developers
:exclamation: hopefully those are not doing any sql development :exclamation:
😎
November 7, 2016 at 10:53 am
A further question, if function are not worked efficiently, why T_SQL has this table valued functions?
In which case we should create functions?
I can think of is for calculation, parse, and then code reuse.
November 7, 2016 at 11:59 am
This is a bogus question.
There are times when you need table-valued functions, and even cursors, which a lot of developers strongly dislike. Sometimes, using a cursor is the best solution to the problem. It's one of those "It depends" questions. If you have worked with SQL long enough, you know the strengths and weaknesses of each.
November 7, 2016 at 12:17 pm
sqlfriends (11/7/2016)
A further question, if function are not worked efficiently, why T_SQL has this table valued functions?In which case we should create functions?
I can think of is for calculation, parse, and then code reuse.
Sometimes your T-SQL is going to be inefficient anyways and will work on single or low numbers of rows, so since you're getting hit with the so called "RBAR" penalty anyways, then scalar functions or other slow constructs wouldn't have the impact otherwise encountered in set oriented programming scenarios. In these cases I think its correct for Microsoft to offer the choice.
November 7, 2016 at 1:51 pm
sqlfriends (11/7/2016)
A further question, if function are not worked efficiently, why T_SQL has this table valued functions?In which case we should create functions?
I can think of is for calculation, parse, and then code reuse.
Flexibility is needed in some cases. Also Microsoft had to add them so they could have a "feature compete" with other database systems.
As for code reuse, here's a Guruism for you:
(almost) Anything that allows a developer to slap together SQL Server code more
quickly is inversely proportional to the performance and scalability of said code.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 8, 2016 at 2:05 pm
I am not sure exactly what you want. I see you used camel case to make the the code harder to read (Google it and see the research; your I jumps to the uppercase letters). The phrase "are related to" is pretty vague. Do they have a foreign key or something? If you would followed forum rules and basic netiquette, then we would have some DDL to play with. Or just skeleton DDL instead of a vague narrative.
Then you talk about fields (columns are not fields fields are parts of columns in SQL. Did you read the standards?). Since SQL is based on predicates and logic, we do not use flags. These are like fried babies at a vegan restaurant 🙁 yes, that kind of fundamentally bad.
Then the only solution you come up with our functions (procedural code)! We do not like to use those either. And they will not be very effective either.
This sounds like you need PK–FK structures of some kind for which we do not have enough information. This sounds like it should be a straight outer join problem and I agree with Kevin.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply