64
votes
What is the best practice about microservice architecture for consuming many stored procedures in the same database?
I get the feeling that some decision maker in your company heard about the buzzword "micro-service" and decided that you need to have them as well, regardless of if they actually solve a ...
- 75.7k
13
votes
Accepted
DB Queries in code or use Stored Procedures
There's a few things to consider here, which I'll try to explain in enough detail to help you understand the problem space. Personally, for RDMS', I prefer using stored procedures, reasons for which I'...
- 879
12
votes
Accepted
When should I use stored procedures?
You have a couple of perfectly good scenarios already.
There are lots of other reasons too. EF is really good at CRUD and at pretty straight forward reporting. Sometimes, though, EF is not the ...
- 2,398
7
votes
How to do versioning for stored procedure?
You have, broadly speaking, two choices:
Make the stored procedure backward compatible,
Add a second, v2, stored procedure to the database and leave the original unchanged.
The first approach can be ...
- 39.4k
6
votes
How to do versioning for stored procedure?
Stored procedures don't support versioning. You will need to make a version of your database with two sprocs with the version in the name
mySproc_v1()
mySproc_v2()
Your API can support versioning in ...
- 78.6k
6
votes
Accepted
Determining the best way(s) of adding unit tests to a large project that makes good use of stored procedures
A stored procedure and the C# code that uses it are different units, so a single unit test can not test them both. You'll need separate unit tests:
Unit tests that mocks the stored procedure and ...
- 12.1k
6
votes
DB Queries in code or use Stored Procedures
The important thing is to have an abstraction layer between the raw SQL and the business logic.
In the old days this would be a stored proc. All the apps would connect directly to the db and call the ...
- 78.6k
5
votes
Encrypt data in code or stored procedures?
In general I would advise against using stored procedures. I've worked at a company that had a lot of logic in stored procedures and it made it really difficult to reason about and test. We ended up ...
- 9,207
5
votes
Stored procedures or ORM in web?
The problem with Stored Procedures in your case are the multiple databases.
As you point out. if you have to change a sproc you will then have to deploy that change to every database. Where as if ...
- 78.6k
5
votes
Accepted
DDD: should entity method use repository for stored procedures (not CRUD)?
The advantage of having the Method on the vehicle class is that it matches your business language. "I want to reserve the vehicle please!"
The disadvantage is that whenever you have a vehicle, you ...
- 78.6k
4
votes
quality management on SQL stored procedures
I would say there is little comparability between the SQL environment and that of standard programming languages in the C tradition.
A stored procedure of 2k lines is certainly a handsome size, but ...
- 10.3k
3
votes
DDD: should entity method use repository for stored procedures (not CRUD)?
I'm personally not in a group of people who think that Entities should not access Repositories. So to me, the answer to your question is "Yes, sure, go ahead."
But in case you don't want to do that, ...
- 37.7k
3
votes
Accepted
How should I handle Stored Procedure parameters that need to be the same length as a column?
You must declare the variable as being of the type of a table's column. That way if the table column changes the stores procedure needs not change.
v_myvar owner.table.column%type;
- 39.4k
2
votes
When should I use stored procedures?
Perhaps it makes sense, to turn the question around and find usecases where only stored procedures could do, what you want to achieve. Perhaps there are really use cases, where stored procedures stand ...
- 9,583
2
votes
Stored procedures or ORM in web?
Stored procedures vs. ORM isn't a clear and cut decision. You will get answers on both sides of that question.
Stored procedures will always have an advantage over ORM in that they can be precompiled ...
- 26.7k
2
votes
Directly query database vs. Using web service
a WPF application that will deploy to ~50 users
Each user will probably make about 25 read/writes a day.
I have everything working in Azure but my issue/fear/question is I'm directly calling stored ...
- 54.9k
2
votes
Accepted
Encrypt data in code or stored procedures?
The concern isn't so much about where you perform encryption/decryption operations, but how you are managing keys, encrypted data, and how you actually go about the encryption itself.
The immediate ...
- 26.5k
2
votes
Testing Stored procedure correctness
You test the correctness (in a unit test, I suppose) the same way you would do it in any other layer: you provide some test data where you exactly know what the outcome of the calculation should be, ...
- 212k
2
votes
Accepted
Multiple stored procedures or 1 with parameter
You are right that changing essentially identical code is a code smell.
The question is why does it smell?
The answer is rarely because someone copied and pasted code (although you should definitely ...
- 16.3k
2
votes
How to do versioning for stored procedure?
First and foremost, use the right tool for the job. Don't use the database to maintain versioning, use your source control tool (e.g. git) to maintain the revisions of the stored procedure in a ...
- 12.4k
2
votes
How can I trace back the original table of a column?
If you're hopping between engines and moving through many layers of queries and views, then it's very unlikely you'll find an automated solution that can trace the dependencies.
I seem to remember ...
- 10.3k
1
vote
How to write robust or alternative to TSQL data processing routines in SqlServer?
Your specific table variable example isn't literally true since table types exist (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql). So if you have an exact same ...
- 111
1
vote
How to do versioning for stored procedure?
I don't know which database you're using, but is it possible to define stored procedures in different schemas (essentially namespaces) and give them access to the underlying data through synonyms? At ...
- 17.1k
1
vote
DB Queries in code or use Stored Procedures
See Databases: Where should the application logic run?
It depends on the amount of data it concerns and how complicated the request is. Security and privacy are other aspects. Think of applicable ...
- 111
1
vote
Single Stored Procedure Per Table Update or Per Column Update
Neither.
Depending on the database you might be able to provide granular permission levels for your users.
Stored procedures should not be used in this case as you are implementing a security measure ...
- 111
1
vote
Optimal use of hibernate with mysql's stored procedures
You don't need stored procedures to prevent SQL injection. Sure it provides one more barrier between the database and the outside world, but if you use HQL parameterized queries, the criteria API etc. ...
- 1,960
1
vote
Stored procedures or ORM in web?
Interesting question and agreeing with John Wu there's no clear cut "right" answer it depends on priorities and the overall skill of the team. Keeping the teams' skill level in C# and the RDBMS has a ...
- 6,152
1
vote
SQL-Style name of variables in a stored procedure
You can use same name to parameter as attribute of a table,but it is not a good way.
It might be ambiguous enough in some procedures/functions that they’re problematic too.
you can use following way:...
- 251
1
vote
How should I handle Stored Procedure parameters that need to be the same length as a column?
One drawback of using overly long columns is in run-time memory allocation. SQL Server will use half the declared length when calculating how much memory to request. So for varchar(100) it will be 50 ...
- 903
1
vote
Generating reports from large datasets
I deal with a very similar situation where we take a large amount of relational data, filter and scrub it down to a smaller subset, and then create the highest level aggregates we can to output data ...
- 26
Only top scored, non community-wiki answers of a minimum length are eligible