Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2012 : Transact-SQL - Stored Procedures

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/30/2014 8:32:50 PM

A stored procedure is a block of T-SQL or .NET code that is stored within a database. This code is similar to other procedures in other languages because it accepts parameters as inputs and can provide output to the calling application or user. Stored procedures make building database applications easy.

Stored procedures contain a header and a body. In the header, you define the input and output parameters. In the body is the code. For example, the following CREATE PROCEDURE statement creates a procedure that will take a pet name and return the microchip ID for that pet:

CREATE PROCEDURE GetID
@name VARCHAR(50)

AS

BEGIN

SELECT MicroChipID FROM Pets WHERE pet_name=@name

END

Parameters are optional. In this example, you need to specify a name. To execute this procedure, you can issue the following statement:

EXEC GetID 'Roxy'

The return the value is 1A8AF59, which is the value of Roxy’s microchip.

Creating Stored Procedures Using Templates

SSMS provides a template to use when creating a new stored procedure. To view this template, navigate to the Stored Procedures node within the Programmability node of the VetClinic database. Select New Stored Procedure from the context menu. This will open a Query Editor window with the following template:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Author,,Name>
-- Create date:  <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
  -- Add the parameters for the stored procedure here
  <@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
     <Default_Value_For_Param1, , 0>,
  <@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
     <Default_Value_For_Param2, , 0>
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 <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Having the syntax already present is helpful, but SSMS makes working with templates easy by giving you a dialog box to use to fill in the values. To launch the Specify Values for Template Parameters dialog box, select this option from the Query menu in SSMS. Figure 1 shows this dialog box.

images

Figure 1. Input dialog box for templates

Once you fill the values in the dialog box and click OK, the template automatically updates the CREATE STORED PROCEDURE text in the Query Editor with the data you provided in the dialog box.

Templates make it very easy to create procedures. Many templates are available for SQL Server. To access the list of templates, select Template Explorer from the View menu in SSMS. This will launch a Template Browser window, shown in Figure 2.

images

Figure 2. Template Browser window in SSMS

Note that there are two buttons on the top of this dialog box; the first one shows SQL Server templates, and the second shows Analysis Services templates. To use a template, simply double-click it, and the template will load in a new Query Editor window.

Modifying Stored Procedures

SSMS does a great job of enumerating all the stored procedures within the database. They are all listed under the Programmability node, which is under a specific database. To modify a stored procedure, you need to issue an ALTER STORED PROCEDURE statement. To modify the GetID procedure created earlier using SSMS, select Modify from the context menu of the GetID stored procedure. This will launch a Query Editor window with the ALTER STORED PROCEDURE text in it. The actual script generated by SSMS is as follows:

USE [VetClinic]
GO
/** Object:  StoredProcedure [dbo].[GetID]    Script Date: 04/29/2009 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetID]
@name VARCHAR(50)
AS
BEGIN
SELECT MicroChipID FROM Pets WHERE pet_name=@name
END

SSMS saves you from a lot of typing when you are working with stored procedures.

System Stored Procedures

If you navigate around SSMS and the Programmability node as described earlier, you may notice a System Stored Procedures folder. There are quite a number of these, and each serves its own purpose within SQL Server. For example, the SQL Server Agent job scheduler does not have native DDL statements for creating jobs, so it uses system stored procedures to create and manage jobs.

Note System stored procedures usually start with the characters sp_. Thus, it is a good idea to not start your stored procedures with the same three characters.

Other -----------------
- SQL Server 2012 : Transact-SQL - Transactions
- SQL Server 2012 : Transact-SQL - Data Manipulation Language (part 2)
- SQL Server 2012 : Transact-SQL - Data Manipulation Language (part 1)
- SQL Server 2012 : Transact-SQL - The VetClinic Sample Database Revisited, Data Types
- Microsoft Sharepoint 2013 : Claims Authentication and Oauth - Server-to-Server Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 3) - App Authentication - App Catalog App Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 2) - App Authentication - SharePoint Store App Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 1) - Cloud App Model, OAuth
- Microsoft Sharepoint 2013 : User Authentication (part 4) - Using Claims-Based Identity - Federated User Authentication Process
- Microsoft Sharepoint 2013 : User Authentication (part 3) - Using Claims-Based Identity - Understanding the User Authentication Process and Authentication Providers
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
Trailers Game
- The Banner Saga 2 [PS4/XOne/PC] PC Launch Trailer
- Welkin Road [PC] Early Access Trailer
- 7th Dragon III Code: VFD [3DS] Character Creation Trailer
- Human: Fall Flat [PS4/XOne/PC] Coming Soon Trailer
- Battlefleet Gothic: Armada [PC] Eldar Trailer
- Neon Chrome [PS4/XOne/PC] PC Release Date Trailer
- Rocketbirds 2: Evolution [Vita/PS4] Launch Trailer
- Battleborn [PS4/XOne/PC] 12 Min Gameplay Trailer
- 7 Days to Die [PS4/XOne/PC] Console Trailer
- Total War: Warhammer [PC] The Empire vs Chaos Warriors Gameplay Trailer
- Umbrella Corps [PS4/PC] Mercenary Customization Trailer
- Niten [PC] Debut Trailer
- Stellaris [PC] Aiming for the Stars - Dev. Diary Trailer #1
- LawBreakers [PC] Dev Diary #4: Concept Art Evolutions
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer