Questions tagged [stored-procedures]
The stored-procedures tag has no usage guidance.
62
questions
174
votes
21
answers
162k
views
Stored Procedures a bad practice at one of worlds largest IT software consulting firms?
I'm working at a project in one of the world's top 3 IT consulting firms, and was told by a DBA that company best practice's state stored procedures are not a "best practice". This is so contrary to ...
53
votes
6
answers
90k
views
Pros and Cons of holding all the business logic in stored procedures in web application [duplicate]
In some organization I worked for web applications are developed basing all the business logic in Database stored procedures. For example, use html for view and servlet as controllers to divert the ...
- 1,027
45
votes
6
answers
16k
views
Do stored procedures violate three-tier separation?
Some colleagues of mine have told me that having business logic in stored procedures in the database violates the three-tier separation architecture, since the database belongs to the data layer ...
- 39.4k
32
votes
7
answers
14k
views
How to suggest using an ORM instead of stored procedures?
I work at a company that only uses stored procedures for all data access, which makes it very annoying to keep our local databases in sync as every commit we have to run new procs. I have used some ...
- 15.7k
26
votes
5
answers
4k
views
Do ORMs enable the creation of rich domain models?
After using Hibernate on most of my projects for about 8 years, I've landed on a company that discourages its use and wants applications to only interact with the DB through stored procedures.
After ...
- 1,022
22
votes
6
answers
20k
views
When should I use stored procedures?
If I have all my business logic in code and make use of Entity Framework, in what situations (if any) would I be better moving some business logic to a stored procedure, instead of keeping it all in ...
- 493
16
votes
5
answers
23k
views
When not to use ORM and prefer stored procedures?
I am using PetaPoco micro-ORM. It is indeed very easy and secure to work with databases using ORM tools, but the only thing I hate is extra code. I used to put most of the code in the database itself ...
- 4,378
12
votes
5
answers
7k
views
What is the best practice about microservice architecture for consuming many stored procedures in the same database?
I have to build a micro service for my company, the backend is an Oracle database, but the micro service must consume five (5) stored procedures that already exist in the database (as shown in the ...
- 139
12
votes
3
answers
3k
views
How to TDD that the correct results are returned
I'm starting a new project, and trying very very hard to use TDD to drive the design. I've been pushing for years, and finally got approval to spend the extra time on this project to use it while I ...
- 8,063
11
votes
4
answers
2k
views
Stored procedure Naming conventions? [closed]
One of our senior developers has stated that we should be using a naming convention for stored procedures with an "objectVerb" style of naming such as ("MemberGetById") instead of a "verbObject" type ...
- 2,013
8
votes
3
answers
7k
views
DB Queries in code or use Stored Procedures
Some of my colleagues tells me that using stored procedures in the database adds to much business logic in the database, and that you should keep the data separate from logic. Other colleagues tells ...
- 390
8
votes
3
answers
4k
views
Directly query database vs. Using web service
I'm a junior programmer, am working on a WPF application that will deploy to ~50 users.
We basically are streamlining all of his charting/tracking of client data. Each user will probably make about ...
- 201
7
votes
4
answers
3k
views
Direct database manipulation an anti-pattern?
Recently I learned that some teams have moved all their database manipulation to the actual database through the use of stored procedures. I thought that was pretty clever, since the database becomes ...
- 1,079
7
votes
1
answer
2k
views
Determining the best way(s) of adding unit tests to a large project that makes good use of stored procedures
We work on a fairly large casino/gaming/wallet/lottery platform. It's a turn-key application that is currently in use by 4 clients, and soon to be much more. I've made some bullet points regarding the ...
- 269
6
votes
6
answers
11k
views
What are the disadvantages of using stored procedures, SSIS, and SSRS to implement business applications? [closed]
Recently, it was suggested that many projects (where I work) should be implemented using some combination of stored procedures (on SQL Sever / T-SQL), SSIS, and SSRS.
In one specific project, SSIS ...
- 285
6
votes
1
answer
995
views
Where Should I Keep Stored Procedure Names in my Code?
I've taken over some code at work that has a database repository file that keeps stored procedure names in constants. The file looks like this:
class DatabaseRepository
{
private const string ...
- 780
5
votes
3
answers
1k
views
Stored procedures or ORM in web?
I am working in a Peruvian company that develops desktop accounting software(.net C#).
We have many clients (companies) each customer can create several companies, in addition there is a new database ...
5
votes
2
answers
159
views
Job Scheduling, Stored Procedures
I am working on a polyglot web app project that has functionalities that can be separated, but that rely on each other. I am unfamiliar with the following (what key words to even search for, what ...
- 213
4
votes
2
answers
6k
views
Why put a simple query into a stored procedure in a web service?
I'm working as a junior programmer, and the senior programmer above me has instructed me to follow a certain unofficial policy for constructing new queries on our web development projects. Generally, ...
- 142
4
votes
4
answers
898
views
Using ASP.NET 4 and Stored Procedures [closed]
How popular are stored procedures today, compared to 5 years ago?
I ask this question because the book that I'm reading (an ASP.NET 4 book) has no stored procedures in it. Is it the ASP.NET 4 way ...
- 1,519
4
votes
1
answer
2k
views
Modern practices for stored procedure-based applications
I work in a fairly large and old solution that has many entry points for different kinds of clients, with web sites for public access, web sites for internal access, some web sites and web services ...
- 1,997
4
votes
2
answers
2k
views
Best solution for a large database transaction
I have a COBOL CICS process running on a mainframe legacy system. The process does over 2K DMLs in a highly concurrent DB environment. After each CRUD operation, the result is used to make further ...
- 460
4
votes
3
answers
424
views
Secure DAL Design using Stored Procedures
I normally avoid sProcs as much as possible. I dont like the language be it TSQL or PL/SQL; they seem archaic against Java/Dot-Net which I use. I go for them when a routine needs to fetch a lot of ...
- 155
4
votes
2
answers
5k
views
Java stored procedures in Oracle, a good idea?
I'm considering using a Java stored procedure as a very small shim to allow UDP communication from a PL/SQL package. Oracle does not provide a UTL_UDP to match its UTL_TCP. There is a 3rd party ...
- 276
3
votes
5
answers
7k
views
How to do versioning for stored procedure?
Is there any way to create versioning for stored procedures? I have created an API which fetches data from a database using stored procedures. This API is being consumed from a mobile app. I released ...
- 177
3
votes
2
answers
334
views
Implementing set of processes in a stored procedure or through the code?
I want to know what's the suitable method to implement the following case (best practice).
If i make a set of processes like this:
select data from set of DB tables.
loop on the selected result.
...
- 1,414
3
votes
2
answers
4k
views
DDD: should entity method use repository for stored procedures (not CRUD)?
Imagine you have a Vehicle entity in your domain model. Vehicle entity has Reserve method that put vehicle in "reserved" state and do another stuff. But Reserve method have to do some checking first ...
3
votes
2
answers
724
views
Forced to write Stored Procedures
Can you think of some reasons that the management force the developers to write and call Stored Procedures instead of inline SQL statements directly?
Even a very simple CRUD statement, writing a ...
- 1,160
3
votes
1
answer
2k
views
Established coding standards for pl/pgsql code
I need to standardize coding practices for project that compromises, among others, of pl/pgsql database, that has some amount of nontrivial code.
I look for:
Code formatting guidelines, especially ...
- 406
3
votes
1
answer
244
views
Should a stored proc try to do more, in the attempt to avoid php mysql back and forth overhead
I have a REST architecture, running PHP on the server side which store
and query a Mysql database.
I am re-evaluating one architecture design decision:
DECISION to re-evaluate:
In an attempt to ...
- 225
3
votes
2
answers
308
views
Stored Procedure performance
I'm working on upgrading an application written by a former developer at my work and I've been converting all the SQL queries in the code into Stored Procedures. I'm doing this with the idea that it ...
- 173
3
votes
1
answer
3k
views
Calling MSSQL stored procedure from Zend Controller ? Any other approaches?
MSSQL and DB, Zend as PHP Framework,
I am using this way to call SP with I/P Parameters and to get O/p Parameters.
It seems I am writing SQL code in PHP.
Any other good approaches?
$str1 = "...
- 131
3
votes
0
answers
3k
views
Executing stored procedure in EntityFramework on SaveChanges()
I'm using Entity Framework (Code first, or rather code only, database first) as my DAL. Among other things it's used in WCF service that inserts some new data to database. For most cases the ...
- 131
2
votes
5
answers
837
views
How does a query execution plan affect query execution?
I have read some articles on SQL Server query optimization. The point I get is SQL Server generates a query execution plan for each stored procedure when it's executed for the first time. Those ...
- 683
2
votes
5
answers
1k
views
Stored Procedures, ORMs and other application layers
I'm just starting out on a project to develop a new, fairly substantial web application which has an underlying MSSQL database. We're hiring a team of developers to write the application (in .NET) and ...
- 131
2
votes
2
answers
6k
views
A CLR SQL Server Stored Procedure calling an ASP.NET Web API
We are developing a big system with SQL Server database, ASP.NET Web API 2.2 services and another external services.
We need to load more data on a table while we process the current data on it. To ...
- 157
2
votes
1
answer
267
views
Best practices for scope of returned data per stored procedure
I have the following situation:
table1
------------
ID Name Param1 Param2 Param3 RegistrationID
1 Test Value1 Value2 Value3 101
table2
------------
RegistrationID LangID Value
101 ...
- 773
2
votes
2
answers
2k
views
multi-language system and security with php
I'm coding a new site. İt's like StackExchange, a social site and a blog. I try to create a multi-language site, however I can't decide how to do it.
I have to use modules, so I must use OOP, while ...
- 121
1
vote
2
answers
206
views
When to really focus on performance?
We've just finishing up the first release of a database driven web application, which is now in regression testing. The application has an advanced search with many different filtering criteria. When ...
- 279
1
vote
2
answers
2k
views
Encrypt data in code or stored procedures?
I'm working on a Web App where the data is quite sensitive and is required to be encrypted before it is stored in the database.
Now there are two ways through which I can achieve this:
Encrypt data ...
1
vote
4
answers
3k
views
is stored procedure having multiple UNION efficient than running all SQL's in parallel?
I have below in my stored proc
if
then
SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3,TABLE1.COLUMN4 FROM TABLE1,TABLE2 where criteria1 (covers functionality 1)
UNION
SELECT TABLE1....
- 141
1
vote
2
answers
1k
views
How should I handle Stored Procedure parameters that need to be the same length as a column?
I'm writing a lot of stored procedures for a SQL Server database that take an id or code that is a CHAR instead of an INT. Most of these haven't changed size in a long time, but when a customer wants ...
- 721
1
vote
2
answers
2k
views
Importance of stored procedures and triggers in building web applications? [duplicate]
Almost in every interview for the position of web developer, they ask about stored procedures and triggers well I am aware from their definition and basic usage but the truths is I have never used any ...
- 890
1
vote
1
answer
233
views
Multiple stored procedures or 1 with parameter
I need some advice...
I started working at a financial business world recently.
Looks like the DB architect uses auto generate function to create stored procs. At the end, we have way too many procs ...
- 13
1
vote
2
answers
278
views
Is using triggers best solution for this scenario
A large SQL transactional database has more than 100 tables (and it will grow). One of them is called Order. Then, there is another table WorkLoad which derives from Order and many other joined table ...
- 113
1
vote
2
answers
78
views
How to write robust or alternative to TSQL data processing routines in SqlServer?
My company has a workflow that involves ingesting huge amounts of raw data from external sources into SqlServer and then interpreting and weaving that data into our own application tables. This ...
- 599
1
vote
2
answers
757
views
Optimal use of hibernate with mysql's stored procedures
I am working on a project (to use as a reference). The project uses mysql, hibernate and spring (REST) and is supposed to be a single microservice.
I have come to find out that limiting user/consumer ...
- 9
1
vote
1
answer
3k
views
Generating reports from large datasets
I'm working on an CRM type application(.NET, SQL Server) that needs to generate reports from large datasets, millions of database rows in a dozen different tables with a lot of aggregation and logic. ...
- 174
1
vote
1
answer
96
views
How to handle multiple "valid after {date}" in a performant way
We work on an Application which displays a duty roster and also some other DateTime related things.
So my Database is full of Relations like in this small Example:
This results in big ...
- 119
1
vote
1
answer
554
views
Is it a good practice to write distinct queries in a single stored procedure based on input parameters
I am going through the stored procedures written throughout our project and see that multiple distinct queries are written inside a single stored procedure and are called by passing different values ...
- 904