Back to Top
The nuts and bolts for
Creative web development

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.

Kevin Koch

Kevin KochKevin Koch is a senior software engineer with over 8 years experience designing and architecting primarily web based applications. Fresh out of college during the nineties he co-founded Task Solutions and developed several projects with the then popular classic ASP.

During the Dot Com boom Kevin left his position as president and joined a new venture to build an enterprise insurance claim system build upon J2EE technology. After the Dot Com crash Kevin schooled himself to become an expert with .NET technology and is currently freelancing his ASP.NET skills to build enterprise n-tier frameworks using advanced OO methodologies.

See All Postings From Kevin Koch >>

Share this Article