February 14, 2023 at 6:37 pm
I currently have multiple databases, many databases represent a single customer, but a couple represent multiple customers. There is a table in each database that holds some columns that then have a string of numbers (generally 1 -4 but sometimes 1 -5) that are how the users answer a question on a multiple choice test. Each number represent one answer, if they do not answer then that is represent by a zero. There are multiple sections of these tests. We then loop through the strings one character at a time and based on the index we can know if they answered correctly or not, and score each section. When, I first started with the company, the scoring mechanism was all done via server side code (at that time VBScript) and I moved it into database functions stored in each database, as it is much faster and also easier to maintain since the business logic code can be updated and we don't need to worry about changes to the scoring. Except the scoring is based on a bell curve of the general population and that bell curve has shifted over the last few years and now I am told that I need to adjust for this. I was wondering if there was a better way to handle this in the future, where we can change a single function that then will be able to score off any database. Is it possible to have a function that is stored somewhere other than in Programmability-> Functions -> Scalar-valued Functions of each database like in the master database and then use in all databases?
February 15, 2023 at 1:06 pm
I once built a system that could query for query text and then execute that. It was a way to intentionally allow people to inject SQL code into the system (yeah, I was probably dumb for doing it). However, it was a royal pain in the bottom to maintain. I think simply adjusting the code is the way to go.
"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
February 16, 2023 at 5:53 am
This was removed by the editor as SPAM
March 1, 2023 at 12:12 pm
I faced similar kind of issue last time
March 1, 2023 at 11:06 pm
I faced similar kind of issue last time
Since half of your replies have been flagged as SPAM and they all seem to be in the same vein of acknowledgement with no useful information , I'm sure you'll want to redeem yourself. If you faced such a situation, what did you actually do about it?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2023 at 1:53 pm
Generally speaking… Yes, you can create functions in the master database and referent them from any other database using the 3 part, fully qualified name.
That said, if the function definition references specific tables in the different databases, you’ll have a difficult time making that work, as you cannot use dynamic sql in functions.
March 2, 2023 at 6:52 pm
Is it possible to have a function that is stored somewhere other than in Programmability-> Functions -> Scalar-valued Functions of each database like in the master database and then use in all databases?
For something like this, I would AVOID using the master database at all costs. Instead, make a UTIL database and put your functions there. Then, AVOID 3 part naming at all costs and create a synonym in each database that points at the UTIL database and can be called from each database using 2 part naming.
That will also mean that a migration to a new server will not only be safer but it'll be a whole lot easier. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply