Developing T-SQL Stored Procedures to Deal with Lookup Tables

(Page 1 of 5 )

In this article, I shall go through a set of Transact-SQL stored procedures which are very frequently used for CRUD operations on lookup tables. My upcoming articles will deal with DAL (Data Access Layers) in .NET based on these stored procedures.

As I wanted to demonstrate stored procedures which do not modify existing data in AdventureWorks/Northwind, I created a table “emp” with the following columns and data types:

• Empno (int)
• Ename (nvarchar(50))
• Sal (float)
• Deptno (int)

I pushed the following sample rows into that table:

How to insert a row into a table using a T-SQL stored procedure

The following is the stored procedure which deals with inserting a particular row into a table. 

CREATEPROCEDURE [dbo].[p_emp_Insert]
      @empno int,
      @ename varchar(50),
      @sal float,
      @deptno int
AS
INSERTINTO [dbo].[emp] (
      [empno],
      [ename],
      [sal],
      [deptno]
)VALUES (
      @empno,
      @ename,
      @sal,
      @deptno
)
RETURN

The above is a stored procedure named “p_emp_insert” which accepts four parameters (according to the columns available in the table).  Once all the parameters are passed in, it executes an INSERT statement to insert those values in the form of a row.

To test the above stored procedure, you can issue the following statement in Query Analyzer or SQL Server Management Studio.

executedbo.p_emp_Insert 2001, 'zzz', 4500, 20

The next section will give the code for updating a row.

Next: How to update/delete a row in a table using a T-SQL stored procedure >>
 

More ASP.NET Articles
More By Jagadish Chaterjee

Comments On: Developing T-SQL Stored Procedures to Deal with Lookup Tables

· Hello guys! this is a simple article on developing most frequently used T-SQL stored...   >>> Post your comment now!