Returning Values from Stored Procedures in ASP.NET

When developing larger scale applications it is quite likely that at some point you’ll be working with Microsoft’s SQL Server. Being the enterprise database server of choice for Microsoft based applications odds are you’ll be using either it or MS Access for smaller applications.

During the initial phases of your application framework design you’ll need to decide some best practices and patterns you want to implement when talking back and forth between SQL Server. This is usually where you decide if you’ll be placing all SQL syntax logic into stored procedures or embed them into your application’s code.

Stored procedures are often the method of choice since they have several advantages. The main advantage is that your SQL syntax is not encapsulated in application code. A simple change to some SQL syntax will mean a recompile and redeployment which is an unnecessary procedure when all you want to do is change some simple SQL statement.

Another advantage is speed. In addition to being able to deploy your SQL logic separately from your application code SQL Server can cache and optimize the commonly used and executed stored procedures which will improve your database transaction performance.

If you decide to take the stored procedure route it won’t be long before you encounter 2 common requirements in your application’s database layer. The first requirement usually arises from your GUI developers when they ask you how they can select an item right away after it is created. In other words, give me the primary key from the table where the object was just created.