Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2008 : Administering Database Objects - Working with Stored Procedures

7/2/2011 11:43:43 AM
You can find stored procedures, under the Programmability folder for each database in Object Explorer. Stored procedures are routines saved in the database that contain business logic used to perform predefined actions and return information about the action that was performed. If you can write a set of code to run in a batch statement, you can most likely encapsulate the code in a stored procedure. A stored procedure can accept input parameters; perform a number of actions using both DDL and Data Manipulation Language (DML) statements, and return output to the client in the form of a scalar value, one or more result sets, or output parameters. A stored procedure can also return an execution status indicating the success or failure of the procedure.

Stored procedures provide many benefits when executing T-SQL including code reusability. You can code logic in stored procedures and call the stored procedure from any application that requires the same information. Stored procedures also provide a layer of abstraction from the application. You can easily change the code within the stored procedure without having to make any changes to the application. You also reduce the network traffic that is sent between the client and the database server. Instead of sending an entire query or batch of code over the network, you will only need to send a single line to execute the procedure. Stored procedures often lead to a performance increase because the query optimizer compiles a query plan for the stored procedure the first time it is executed, and the query plan is then reused for subsequent executions. The reason I say a compiled query plan often leads to increased performance is because in some cases the query plan that the query optimizer chose when the stored procedure was first executed may not be the best plan for subsequent executions with different parameters and actually causes a performance hit. Stored procedures enhance security by allowing you to grant rights to execute the procedure instead of giving access directly to the base tables. Also, by using parameterized queries, you reduce the chances of a SQL injection attack. Keep in mind that just because you use a stored procedure does not mean you are immune to a SQL injection attack. Bad coding techniques, such as dynamic SQL, are prone to a SQL injection no matter where the code is encapsulated.

When you create a stored procedure, you should not use the sp_ naming convention because this prefix is designated for SQL Server system stored procedures. If you create a procedure with the sp_prefix, it may conflict with a current or future system stored procedure. Listing 1 shows how to create a simple stored procedure that accepts a group name as a parameter and returns all the departments in that group.

Example 1. Syntax to Create and Execute a Stored Procedure That Accepts a Parameter and Returns a Result Set
USE AdventureWorks2008
GO

CREATE PROCEDURE dbo.DepartmentNameByGroup_Select (@GroupName nvarchar(50))
AS
SELECT DepartmentID,
Name AS DepartmentName
FROM HumanResources.Department
WHERE GroupName = @GroupName

GO
--Execute the stored procedure
EXEC dbo.DepartmentNameByGroup_Select 'Executive General and Administration'


You can see the results of Listing 1 in Figure 1. You would typically create a stored procedure like the one in Listing 1 to bind to a drop-down list in an application.

Figure 1. Stored procedure execution results using the code in Listing 1

You can use an output parameter to capture and return a single value to the code that calls the stored procedure. However, you can have multiple output parameters within a single stored procedure. The following example in Listing 2 alters the stored procedure that we created in Listing 1 to add an output parameter named @DepartmentCount that is used to hold the number of departments for the group being returned. (Notice the OUTPUT clause in the parameter definition.)

When you run a procedure with an output variable, you must define a variable in the calling code to hold the return value. In Listing 2, we have defined a variable called @NbrOfDepartments. That is the variable that receives the value from the procedure's output parameter.

You must also use the OUTPUT clause following the variable that will be used to capture the return value, or you will end up with a NULL variable in the calling code. So you must use the keyword OUTPUT in the parameter definition, as well as when actually passing the parameter to the procedure.

Example 2. Syntax to Alter the Stored Procedure Created in Listing 1 to Use an Output Parameter
USE AdventureWorks2008
GO
ALTER PROCEDURE dbo.DepartmentNameByGroup_Select
(@GroupName nvarchar(50),
@DepartmentCount int OUTPUT)
AS
SELECT DepartmentID,
Name AS DepartmentName
FROM HumanResources.Department
WHERE GroupName = @GroupName
GROUP BY DepartmentID,
Name;

SELECT @DepartmentCount=COUNT(1)
FROM HumanResources.Department
WHERE GroupName = @GroupName;
GO

--Execute the stored procedure
DECLARE @NbrOfDepartments int

EXEC dbo.DepartmentNameByGroup_Select
'Executive General and Administration',
@NbrOfDepartments OUTPUT

SELECT 'There Are ' +
CAST(@NbrOfDepartments as varchar(50)) +
' Departments In This Group.'


As you can see in Figure 2, the code from Listing 2 contains two result sets. The output parameter has no visual effect on the first result set; you can only see the output returned from the stored procedure by specifying the variable used to capture the output in a second query.

Figure 2. Stored procedure execution results using the code in Listing 2

You can also use the RETURN clause to return information to the code that calls a stored procedure. The RETURN clause is more limited than using output parameters in that you can only have a single RETURN clause per stored procedure that returns an integer value. The RETURN clause also immediately stops the execution of a stored procedure and returns control to the calling code.

Listing 3 alters the stored procedure created in Listing 1 to use the RETURN clause to return the number of departments for a group. Notice that now we are setting the @NbrOfDepartments variable equal to the results of the stored procedure instead of using it as a parameter as we did with the output parameters. The result set is exactly the same as what was shown in Figure 2 .

Example 3. Syntax to Alter the Stored Procedure Created in Listing 1 to Use the RETURN Clause
USE AdventureWorks2008
GO
ALTER PROCEDURE dbo.DepartmentNameByGroup_Select
(@GroupName nvarchar(50))
AS

SELECT DepartmentID,
Name AS DepartmentName
FROM HumanResources.Department
WHERE GroupName = @GroupName
GROUP BY DepartmentID,
Name;

RETURN (SELECT COUNT(1)
FROM HumanResources.Department
WHERE GroupName = @GroupName);

GO

--Execute the stored procedure
DECLARE @NbrOfDepartments int

EXEC @NbrOfDepartments =
dbo.DepartmentNameByGroup_Select 'Executive General and Administration'

SELECT 'There Are ' +
CAST(@NbrOfDepartments as varchar(50)) +
' Departments In This Group.'


Because a stored procedure's query plan is cached, you may find yourself needing to recompile a stored procedure in order to benefit from recent changes to your database, such as an added index. A stored procedure is automatically recompiled the first time it is run after SQL Server is restarted as well as when the structure of an underlying table is changed, but adding an index does not force a recompile.

You can use the sp_recompile system stored procedure to force a stored procedure to produce a new query plan the next time it is executed (see Listing 4). The sp_recompile procedure takes an object name as an input parameter. If you pass in the name of a stored procedure, it will be recompiled the next time it runs; if you pass in the name of a table, any stored procedure that runs against that table will be recompiled.

Example 4. Syntax to Force the dbo.DepartmentNameByGroup_Select Stored Procedure to Generate a New Execution Plan
USE AdventureWorks2008
GO
EXEC sp_recompile 'dbo.DepartmentNameByGroup_Select'
GO

To remove a stored procedure from the database, you can issue the DROP PROCEDURE statement followed by the procedure name, as shown in Listing 5.

Example 5. Syntax to Drop a Stored Procedure from the Database
DROP PROCEDURE dbo.DepartmentNameByGroup_Select

You also have the option to execute a stored procedure automatically whenever SQL Server starts by using the sp_procoption system stored procedure. In order to enable a stored procedure for auto execution, it must exist in the master database and cannot contain input or output parameters. The code in Listing 6 shows the syntax to enable a stored procedure for auto execution. In order to disable auto execution on the stored procedure, change the @OptionValue to off.

Example 6. Syntax to Enable Stored Procedure Execution on SQL Server Startup
sp_procoption @ProcName = 'uspSystemMaintenance',
@OptionName = 'startup',
@OptionValue = 'on'
Other -----------------
- Active Directory Domain Services 2008 : Manage Active Directory Domain Services Data - Disable a User Object
- Active Directory Domain Services 2008 : Manage Active Directory Domain Services Data - Add User to Group
- Active Directory Domain Services 2008 : Manage Active Directory Domain Services Data - Move User Object
- BizTalk 2009 : Wcf Adapters: Standardizing Your Integration Endpoints - Using the WCF-WSHttp Adapter
- BizTalk 2009 : Wcf Adapters: Standardizing Your Integration Endpoints - Using Metadata Exchange
- BizTalk 2009 : Wcf Adapters: Standardizing Your Integration Endpoints - Securing Your Endpoints
- Microsoft PowerPoint 2010 : Adding a Digital Signature to a Macro Project & Assigning a Macro to a Toolbar or Ribbon
- Microsoft PowerPoint 2010 : Expanding PowerPoint Functionality - Controlling a Macro
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Configure Offline File Caching
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Share Folders
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Implement Permissions
- Microsoft Dynamics CRM 2011 : Working with Leads and Opportunities - Creating an Opportunity
- Microsoft Dynamics CRM 2011 : Qualifying a Lead & Disqualifying a Lead
- Microsoft Dynamics CRM 2011 : Understanding Leads and Opportunities & Creating a Lead and Tracking Lead Sources
- Performing an Exchange Server 2003 Installation
- Planning a Microsoft Exchange Server 2003 Infrastructure : Preparing Forests and Domains
- Planning a Microsoft Exchange Server 2003 Infrastructure : Installation Considerations
- SharePoint 2010 : Securing Information - Securing Sites
- SharePoint 2010 : Securing Information - Securing Site Collections
- WCF and BizTalk 2009 (part 2) - Publishing Your WCF Service from the Command Line & Consuming WCF Services
 
 
Most view of day
- Microsoft Excel 2010 : Calculating the Mode (part 4) - Getting the Mode of Categories with a Formula - Using Formula Evaluation
- Microsoft Systems Management Server 2003 : Security - Accounts and Groups
- Microsoft Dynamics AX 2009 : Form Customization (part 2) - Displaying an Image
- Windows Server 2012 : Administering Active Directory using Windows PowerShell (part 2) - Finding Active Directory administration cmdlets
- Monitoring Windows Small Business Server 2011 : Using WSUS Reports
- Microsoft Exchange Server 2010 : Creating and Managing Accepted Domains (part 2) - Creating Accepted Domains
- Windows Server 2008 : Designing the Active Directory Administrative Model (part 2) - Using Group Strategy to Delegate Management Tasks
- Sharepoint 2013 : New Installation and Configuration - Managed Accounts
- Troubleshooting Stop Messages : Common Stop Messages (part 4)
- Microsoft Dynamic AX 2009 : Working with .NET Business Connector (part 2) - Exception Handling, Accessing Data
Top 10
- Microsoft Lync Server 2013 : Director Troubleshooting (part 3) - Synthetic Transactions,Telnet
- Microsoft Lync Server 2013 : Director Troubleshooting (part 2) - DNS Records, Logs
- Microsoft Lync Server 2013 : Director Troubleshooting (part 1) - Redirects, Certificates
- Microsoft Lync Server 2013 : Administration of the Director Role (part 4) - Services Management, Client Version Filter
- Microsoft Lync Server 2013 : Administration of the Director Role (part 3) - Topology Status
- Microsoft Lync Server 2013 : Administration of the Director Role (part 2) - Ports,Firewall Rules
- Microsoft Lync Server 2013 : Administration of the Director Role (part 1) - Services
- Microsoft Lync Server 2013 : Configuring the Director (part 2) - Web Services Ports,Reverse Proxy
- Microsoft Lync Server 2013 : Configuring the Director (part 1) - SRV Records, Web Services FQDN Overrides
- Sharepoint 2013 : SharePoint Designer 2013 (part 2) - Locking Down SharePoint Designer
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro