Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server Stored Procedure Tutorial


A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again.  So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

Take a look through each of these topics to learn how to get started with stored procedure development for SQL Server.

You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.


Last Update: 3/24/2009

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Monday, April 23, 2018 - 1:24:28 AM - mahmoud yasein Back To Top


Good job

Saturday, August 12, 2017 - 7:42:04 PM - Dennisprind Back To Top
Строительные новости тут lakkk.com/

Friday, February 17, 2017 - 2:38:28 PM - Sam Back To Top

 Hello Greg,


Thank You for the tutorial on Stored Procedures, I highly appreciated it.


Can you recommend any book(s) or online resource to  go beyond what you have shon here? I will feel much obliged for your help.


Have a nice day.






Wednesday, July 06, 2016 - 3:11:00 AM - Keval Shah Back To Top

Hi,, i want Udate inner join Stored Procedure Code. Because i tried it, but i can't write properly.

Saturday, May 21, 2016 - 7:45:49 AM - Ravi Back To Top

What is Work of SET NONCOUNT

Describe IN Detail.

Monday, April 11, 2016 - 8:20:05 AM - jonasstuart Back To Top

Example: Creating Stored Procedure


      -- Add the parameters for the stored procedure here




      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.


    -- Insert statements for procedure here

      SELECT * FROM userlogininfo WHERE Password = @PASSWORD





Desired Output: 102       Arunsingh           Vashundhra Ghaziabad [email protected]               123456  7607185995                Admin   1989-02-28

Tuesday, April 05, 2016 - 6:10:33 AM - karthikaqpt Back To Top

 Try this code for validation. Learn sql basics from https://www.youtube.com/watch?v=7Vtl2WggqOg


    @username VARCHAR(20),
    @password varchar(20)


IF EXISTS(SELECT * FROM usertable WHERE username = @username AND password = @password)
    SELECT 'true' AS UserExists
    SELECT 'false' AS UserExists


Wednesday, March 09, 2016 - 6:58:11 AM - Greg Robidoux Back To Top

Hi Damanpreet,

the issue is this part of the stored procedure: if @personid = pr.projectmanagerid.   There is no value yet for pr.projectmanagerid, so you get the error. 

Rewrite as follows:

alter procedure usp_projectcount
@count int, @personid int
set nocount on
if exists (select top 1 projectmanagerid from project where projectmanagerid = @personid)
   (select top (@count) count(pr.projectmanagerid) from person p
   inner join project pr on p.personid = pr.projectmanagerid
print 'no record found'

Wednesday, March 09, 2016 - 2:33:42 AM - Damanpreet Back To Top


I am geeting an error msg 4104 (The multi-part identifier "pr.projectmanagerid" could not be bound.)

Please help in resolving this error.

 alter procedure usp_projectcount
@count int, @personid int
set nocount on
if @personid = pr.projectmanagerid
(select top (@count) count(pr.projectmanagerid) from person p
inner join project pr on p.personid = pr.projectmanagerid
print 'no record found'

Friday, February 26, 2016 - 12:29:44 PM - mounika Back To Top


 Hello sir,

I want to write a stored procedure for login page:


1. If a user is able to login then it needs to check user table if the user is present it return success and retrieve the data which user has access

2.If the user is unable to login then it sends failure and writes the username and expired date in audit table



Thursday, January 28, 2016 - 8:35:36 AM - Greg Robidoux Back To Top

@bhavsinh, when you setup parameters to be passed to a stored procedure you specify the data type, so that is step one in validating the type of input that is passed.  You can then write logic within the stored procedure to further validate the data and then take different paths in the stored procedure based on the the logic you write. 

Look at this for passing parameters: https://www.mssqltips.com/sqlservertutorial/162/how-to-create-a-sql-server-stored-procedure-with-parameters/

You can also use If logic in your stored procedure.  Take a look at this: https://msdn.microsoft.com/en-us/library/ms182717.aspx




Thursday, January 28, 2016 - 3:22:49 AM - bhavsinh Back To Top

hello sir,

in stored Procedure how to give validation by user input

Learn more about SQL Server tools