SQL Server Stored Procedure - SELECT - Example

Source code to create and add sql select stored procedure to catalog

The following example is for creating a simple select stored procedure. You can run it through an explicit call from a host language program or directly from a DBMS query execution shell like SQL Server Management Studio or dbOrchestra.

IF ( OBJECT_ID('dbo.sp_Students_SEL_byPK') IS NOT NULL ) 
   DROP PROCEDURE dbo.sp_Students_SEL_byPK
GO

CREATE PROCEDURE dbo.sp_Students_SEL_byPK
       @student_id    INT        
AS
BEGIN 
     SET NOCOUNT ON 

     SELECT 
            active_flg                   ,
            lastname                     ,
            firstname                    ,
            marital_status_code            
     FROM   dbo.Students (NOLOCK) 
     WHERE  
            student_id = @student_id

END 

GO 

You will note that just like in a SQL SELECT or a SQL VIEW you do not have to use all of the columns available when creating a stored procedure. Additionally, for this stored procedure I used the primary key of the table as the means of access (the WHERE clause). You can write a stored procedure that uses an alternate key as a means of access. 

Executing the sql select stored procedure

Execute sql insert stored procedure

To run the stored procedure you need to supply a value to the student_id variable. The "WHERE" clause in the code above maps this variable to the student_id column in the table.

EXEC dbo.sp_Students_SEL_byPK
@student_id = 2 

GO 

Stored procedure results

active_flg lastname firstname marital_status_code 
----------------------------------------------------
1 JONES Bob NULL 

Rows Returned = 1

Exec Time = 36 ms | Rows Returned = 1 | Rows Affected = 0

Link to schema for Students table