SQL Server Stored Procedure Tutorial
MENU
By: Greg Robidoux
Overview
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.
Comments For This Article
Saturday, March 25, 2023 - 12:18:35 PM - David Gwyn Smith | Back To Top (91048) |
excellent article. |
Monday, April 11, 2022 - 3:22:11 AM - Mahmoud | Back To Top (89989) |
I have write a query that send me a message from my DB, containing some information of my tables. this is the code: USE [Database-name]; GO /*Declare Variables for HTML*/ DECLARE @Style NVARCHAR(MAX)= ''; DECLARE @tableHTML NVARCHAR(MAX)= ''; /*Define CSS for html to use*/ SET @Style += +N'<style type="text/css">' + N'.tg {border-collapse:collapse;border-spacing:0;border-color:#aaa;}' + N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}' + N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#0F3870;}' + N'.tg .tg-9ajh{font-weight:bold;background-color:lightgrey}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}' + N'</style>'; SET @tableHTML = @Style + @tableHTML + N'<H2>CDR Summary</H2>' + N'<table class="tg">' --DEFINE TABLE /*Define Column Headers and Column Span for each Header Column*/ + N'<tr>' + N'<th class="tg-hgcj" colspan="4">CDR</th>' + N'</tr>' + N'</tr>' /*Define Column Sub-Headers*/ + N'<tr>' + N'<td class="tg-9ajh">Total Attempt</td>' + N'<td class="tg-9ajh">Total Duration in Min</td>' /*Define data for table and cast to xml*/ + CAST(( SELECT td = CONVERT(CHAR(8), count(id), 112), '', td = CAST(SUM(DurationInSeconds) / 60 AS NUMERIC(18,2)) , '' FROM [TOneWhS_CDR].[CDR] FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</table>' + N'</br>' + N'</br>' + N'<table class="tg">' --DEFINE TABLE /*Define Column Headers and Column Span for each Header Column*/ + N'<tr>' + N'<th class="tg-hgcj" colspan="2">CDR Main</th>' + N'<th class="tg-hgcj" colspan="2">CDR Invalid</th>' + N'<th class="tg-hgcj" colspan="2">CDR Failed</th>' + N'<th class="tg-hgcj" colspan="2">CDR Partial</th>' + N'<th class="tg-hgcj" colspan="2">CDR interconnect</th>' + N'</tr>' /*Define Column Sub-Headers*/ + N'<tr>' + N'<td class="tg-9ajh">Total Attempt</td>' + N'<td class="tg-9ajh">Total Duration in Min</td>' + N'<td class="tg-9ajh">Total Attempt</td>' + N'<td class="tg-9ajh">Total Duration in Min</td>' + N'<td class="tg-9ajh">Total Attempt</td>' + N'<td class="tg-9ajh">Total Duration in Min</td>' + N'<td class="tg-9ajh">Total Attempt</td>' + N'<td class="tg-9ajh">Total Duration in Min</td>' + N'<td class="tg-9ajh">Total Attempt</td>' + N'<td class="tg-9ajh">Total Duration in Min</td>' /*Define data for table and cast to xml*/ + CAST(( SELECT td = CONVERT(CHAR(8), count(a.CDRId), 112), '', td = CAST(SUM(a.DurationInSeconds) / 60 AS NUMERIC(18,2)), '', td = CONVERT(CHAR(8), count(b.CDRId), 112), '', td = CAST(SUM(b.DurationInSeconds) / 60 AS NUMERIC(18,2)), '', td = CONVERT(CHAR(8), count(c.CDRId), 112), '', td = CAST(SUM(c.DurationInSeconds) / 60 AS NUMERIC(18,2)), '', td = CONVERT(CHAR(8), count(d.CDRId), 112), '', td = CAST(SUM(d.DurationInSeconds) / 60 AS NUMERIC(18,2)), '', td = CONVERT(CHAR(8), count(e.CDRId), 112), '', td = CAST(SUM(e.DurationInSeconds) / 60 AS NUMERIC(18,2)), '' from [table1] a full join [table2] b on a.CDRId = b.CDRId full join [table3] c on a.CDRId = c.CDRId full join [table4] d on a.CDRId = d.CDRId full join [table] e on a.CDRId = e.CDRId FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</table>' + N'</br>' + N'</br>' + N'<table class="tg">' --DEFINE TABLE /*Define Column Sub-Headers 2 RESULT SECTION!!!!!!*/ + N'<tr>' + N'<td class="tg-9ajh">Result</td>' + N'<td class="tg-9ajh" style="background-color: red;"></td>' /*Define data for table and cast to xml*/ + 'MISSING CODE' + N'</table>' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mahmoud El Khatib', @recipients = 'my email', @body = @tableHTML, @body_format = 'HTML', @subject = 'CDR'; -------------------------------------------------------------------------------------------- This code generates a table and information sent to my email with specific design in CSS section.[this is the output of the code above][1] -------------------------------------------------------------------------------------------- In the result section, I want to put a procedure that check my database in a true or false statement, if it's true it will write "All good!" with background green, if it's false it will write "All Bad!" with background red. this is my procedure DROP procedure IF EXISTS spchecktables GO create procedure spchecktables as declare @cdr int declare @maincdrtable int declare @invalidcdrtable int declare @failedcdrtable int declare @interconnectcdrtable int declare @partialcdrtable int select @cdr = count(id) from [TOneWhS_CDR].[CDR] select @maincdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Main] select @invalidcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Invalid] select @failedcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Failed] select @interconnectcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Interconnect] select @partialcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_PartialPriced] if (@cdr = @maincdrtable + @invalidcdrtable + @failedcdrtable + @interconnectcdrtable + @partialcdrtable ) print 'All is good!' else print 'all is bad' go exec spchecktables; -------------------------------------------------------------------------------------------- MY REQUEST IS: how to embed my procedure in my first code? |
Monday, April 23, 2018 - 1:24:28 AM - mahmoud yasein | Back To Top (75756) |
Good job |
Friday, February 17, 2017 - 2:38:28 PM - Sam | Back To Top (46448) |
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.
Regards, SD
|
Wednesday, July 6, 2016 - 3:11:00 AM - Keval Shah | Back To Top (41822) |
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 (41528) |
What is Work of SET NONCOUNT Describe IN Detail. |
Monday, April 11, 2016 - 8:20:05 AM - jonasstuart | Back To Top (41187) |
Example: Creating Stored Procedure CREATE PROCEDURE TESTPROCEDURE -- Add the parameters for the stored procedure here @PASSWORD VARCHAR(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT * FROM userlogininfo WHERE Password = @PASSWORD END GO // OUTPUT: EXEC TESTPROCEDURE '123456'
Desired Output: 102 Arunsingh Vashundhra Ghaziabad [email protected] 123456 7607185995 Admin 1989-02-28 |
Tuesday, April 5, 2016 - 6:10:33 AM - karthikaqpt | Back To Top (41138) |
Try this code for validation. Learn sql basics from https://www.youtube.com/watch?v=7Vtl2WggqOg CREATE STORED PROCEDURE CheckPassword
|
Wednesday, March 9, 2016 - 6:58:11 AM - Greg Robidoux | Back To Top (40896) |
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 |
Wednesday, March 9, 2016 - 2:33:42 AM - Damanpreet | Back To Top (40894) |
Hi, 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 |
Friday, February 26, 2016 - 12:29:44 PM - mounika | Back To Top (40804) |
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 (40528) |
@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 Thanks
|
Thursday, January 28, 2016 - 3:22:49 AM - bhavsinh | Back To Top (40522) |
hello sir, in stored Procedure how to give validation by user input |