Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [stored-procedures]

The tag has no usage guidance.

3
votes
3answers
834 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 ...
-1
votes
1answer
66 views

Single Stored Procedure Per Table Update or Per Column Update

Let's say i have a table (in a large enterprise application) that has about 10 columns. I am limiting any consumer access to the table to only be possible by calling stored procedures. When it comes ...
1
vote
2answers
275 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 ...
2
votes
2answers
2k 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 ...
1
vote
2answers
953 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 ...
5
votes
3answers
463 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 ...
1
vote
1answer
540 views

SQL-Style name of variables in a stored procedure

As seen in the main question. It is not a syntactic problem that I have, or some error in the code. I would like to know what is the correct way to name the parameters variables in a stored procedure....
7
votes
1answer
928 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 ...
15
votes
6answers
9k 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 ...
0
votes
1answer
108 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 ...
1
vote
1answer
954 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. ...
1
vote
4answers
1k 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....
2
votes
4answers
484 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 ...
5
votes
2answers
126 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 ...
6
votes
1answer
357 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 ...
1
vote
1answer
86 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 ...
3
votes
2answers
158 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 ...
0
votes
2answers
860 views

Spring StoredProcedure Inject declared parameters or not?

At work one of my colleague asked to me to change the way a Spring StoredProcedure subclass class was initialised from injecting the parameters with Dependency Injection mechanism (Constructor ...
1
vote
1answer
694 views

Entity Framework 6 and Stored Procedures

I'm new to Entity Framework, and currently developing an MVC 5 application with Code-First Entity Framework 6.1.2. I need to write custom reports. Instead of writing complex linq, I can write a ...
2
votes
1answer
168 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 ...
1
vote
1answer
390 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 ...
0
votes
2answers
5k views

how to do temporary tables in Linq to Entity Framework

Our application queries the database primarily using Linq queries backed by Entity Framework but we sometimes call legacy stored procedures. Sometimes we want to replace an existing stored procedure ...
5
votes
1answer
565 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 ...
2
votes
2answers
5k 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 ...
2
votes
0answers
2k 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 ...
3
votes
3answers
293 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 ...
1
vote
2answers
219 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 ...
-1
votes
2answers
2k views

Storing Dates & Times in SQL [closed]

I have a tool that I am working on which allows people to create meetings. They can select the Date, Time & Timezone that this meeting is occurring in. I need to determine the best way to store ...
2
votes
5answers
753 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 ...
0
votes
1answer
465 views

Stored procedure in asp.net webforms [duplicate]

Previously while working on database in asp.net I was using stored procedure with no tier architecture. So I was including connection string, crud query etc in every button click event. Then I was ...
4
votes
2answers
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 ...
7
votes
4answers
1k 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 ...
3
votes
1answer
184 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 ...
3
votes
2answers
317 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
vote
2answers
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 ...
3
votes
1answer
1k 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
vote
2answers
199 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 ...
0
votes
1answer
965 views

Advanced Search Stored procedure

So I am working on an MVC ASP.NET web application which centers around lots of data and data manipulation. PROBLEM OVERVIEW: We have an advanced search with 25 different filter criteria. I am using a ...
6
votes
6answers
10k 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 ...
2
votes
1answer
1k 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 ...
40
votes
6answers
10k 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 ...
3
votes
2answers
645 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 ...
3
votes
1answer
2k 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 = "...
4
votes
2answers
5k 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, ...
33
votes
6answers
57k 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 ...
12
votes
3answers
2k 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 ...
21
votes
5answers
2k 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 ...
12
votes
5answers
12k 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 ...
0
votes
1answer
681 views

Is it common to have stored procedures disabled on MySQL managed hosting plans?

I'm building an app that I think would benefit from the use of MySQL stored procedures. I know stored procedures have a bad reputation, but I think they suit my application well. Using stored ...
4
votes
2answers
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 ...