The tag has no usage guidance.

learn more… | top users | synonyms

106
votes
23answers
73k 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 ...
35
votes
5answers
7k 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 ...
26
votes
7answers
9k 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 ...
19
votes
6answers
29k 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 ...
18
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 ...
10
votes
4answers
795 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 ...
10
votes
5answers
8k 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 ...
10
votes
3answers
1k 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 ...
7
votes
4answers
596 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 ...
5
votes
1answer
193 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 ...
4
votes
2answers
4k 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, ...
4
votes
6answers
9k 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 ...
4
votes
2answers
728 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 ...
4
votes
1answer
76 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 ...
4
votes
1answer
181 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 ...
3
votes
2answers
293 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. ...
3
votes
4answers
867 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 ...
3
votes
2answers
601 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
165 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
86 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 ...
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 = "...
3
votes
3answers
144 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 ...
3
votes
2answers
4k 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 ...
2
votes
5answers
437 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 ...
2
votes
4answers
112 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 ...
2
votes
1answer
708 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 ...
2
votes
1answer
800 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, ...
2
votes
2answers
147 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 ...
2
votes
1answer
104 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 ...
2
votes
2answers
864 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 ...
1
vote
2answers
194 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 ...
1
vote
4answers
165 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....
1
vote
2answers
993 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 ...
1
vote
1answer
83 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 ...
1
vote
1answer
138 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 ...
1
vote
1answer
340 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 ...
1
vote
0answers
1k 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 ...
0
votes
1answer
33 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 ...
0
votes
2answers
2k 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 ...
0
votes
2answers
2k 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 ...
0
votes
1answer
643 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 ...
0
votes
1answer
553 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 ...
0
votes
2answers
282 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 ...
0
votes
0answers
64 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. ...
0
votes
1answer
312 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 ...
-1
votes
2answers
533 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 ...
-4
votes
0answers
24 views

Error executing stored procedure query in JPA [on hold]

I'm getting a resultset error while trying to fetch data from a stored procedure. Here's the stored procedure: create procedure Proc_Member_Statement @Counter int,@TransDate datetime as declare @...