Lately I have been working with Stored Procedures, I must admit that it’s not a thing that I previously have had lot of experience in. I thought that it might be the case for other developers out there as well. First very and very short “A stored procedure is a subroutine available to applications that access a relational database system”. If you want to know more about the definition of a Stored Procedure then take a look at Wikipedias definition In this short post, I will try to show how to create a really simple stored procedure.
CREATE PROCEDURE sp_getUser(@userId int) AS
SELECT username, email, userid
This is our simple stored procedure, it retrieves user information (username,email,userid) from the table “user”, where the userid is set in the procedure head.
Let’s have a look on how to call the procedure
DECLARE @userId int;
SET @userId = 2;
EXECUTE sp_getUser @userId;
First declare a variable @userId as an int, next set the @userId=2 (we want to retrieve information for the user with id = 2. The next part calss the procedure, where sp_getUser is the procedure we just made, and @userId is the parameter we send to the procedure. The result will be a recordset with username, email and userid for the given user. I hope this very simple example can help getting started with STORED PROCEDURES in MS SQL Server.