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 ...
Bart van Ingen Schenau's user avatar
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'...
e_i_pi's user avatar
  • 859
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 ...
Joel Brown's user avatar
  • 2,388
8 votes

How does a query execution plan affect query execution?

If you're a programmer, you'll notice that SQL doesn't look much like normal code. Normal code is written in what's known as an imperative style: it tells the computer what to do. SQL, on the other ...
Mason Wheeler's user avatar
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 ...
Ewan's user avatar
  • 75.6k
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 ...
Ewan's user avatar
  • 75.6k
6 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 ...
David Arno's user avatar
  • 39.3k
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 ...
Idan Arye's user avatar
  • 12.1k
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 ...
Samuel's user avatar
  • 9,197
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 ...
Ewan's user avatar
  • 75.6k
5 votes
Accepted

is stored procedure having multiple UNION efficient than running all SQL's in parallel?

Running your queries in parallel from your application, and then combining the output will never be as fast as running a tuned query from the database. Do the work where it is most appropriate ...
Adam Zuckerman's user avatar
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 ...
Ewan's user avatar
  • 75.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 ...
Steve's user avatar
  • 8,755
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, ...
Euphoric's user avatar
  • 37.4k
3 votes

How does a query execution plan affect query execution?

Mason Wheeler's answer is very good. There are a few more points to add: When the database engine first executes a query, it doesn't just come up with one execution plan. It comes up with a lot of ...
Walter Mitty's user avatar
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;
Tulains Córdova's user avatar
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 ...
Thomas Junk's user avatar
  • 9,543
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 ...
John Wu's user avatar
  • 26.5k
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 ...
Flater's user avatar
  • 49.7k
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, ...
Doc Brown's user avatar
  • 207k
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 ...
Kain0_0's user avatar
  • 16.2k
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 ...
maple_shaft's user avatar
  • 26.5k
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 ...
Lie Ryan's user avatar
  • 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 ...
Steve's user avatar
  • 8,755
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 ...
Hans-Martin Mosner's user avatar
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 ...
Klaas-Z4us-V's user avatar
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 ...
Dwight J. Browne's user avatar
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. ...
Kayaman's user avatar
  • 1,950
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 ...
mcottle's user avatar
  • 6,142
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:...
AjayGohil's user avatar
  • 241

Only top scored, non community-wiki answers of a minimum length are eligible