.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Creating and Managing Stored Procedure in SQL Server 2008

Posted By:Vishal Nayan       Posted Date: September 01, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Creating and Managing Stored Procedure in SQL Server 2008
 

Creating and Managing Stored Procedure in SQL Server 2008

 

What is stored procedure: They are one or more SQL programs stored in a database as executable object. They can be called interactively, from within client application or from another stored procedure and from within trigger. We can pass parameters to and return from stored procedure to increase their usefulness and flexibility. A stored procedure can return a number or result set and status code.

 

Advantage of using Stored Procedure:

1) Modular Programming:  Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.

2) Function bases access to tables: A user can have permissions to execute a stored procedure without having permission to operate directly on the underlying tables.

3) Reduced network traffic: Stored procedure can consists of many individual SQL queries but can be executed with a single statement. This allows us to reduce the number and size of calls from the client to server.

4) Faster Execution: SP query plan are kept in memory after the first execution. The code doesn't have to be reparsed and repotimized on subsequent executions.

 

Disadvantage of using Stored Procedures

1) Increase in server processing requirement: Using stored procedure can increase the amount of server processing. In a large user environment with considerable activity in the server, it may be more desirable to offload some of the processing to the client side.

2) Business Logic in SP: Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.

Big Question. When to use Stored Procedure: Stored procedure is well suited for 2-tier environment, but the trend is shifting to 3-tier n more environments. In such scenario business logic is often is often handled in some middle tier. So in such scenario, we would like to restrict the stored procedure to performing basic data-related tasks, such as SELECT, UPDATE, DELETE.

For all examples shared below I have used Pubs database. You can download its msi file from here and then attach .mdf file in your Sql Sever 2008.

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Creating Stored Procedure: We need to give the procedure a unique name within the schema and then write the sequence of SQL statements to be executed within the procedure. Following is the basic syntax for creating stored procedures:

 

Expand databaseàSelect Pubs databaseàSelect ProgrammaticallyàSelect Stored Procedureàright click and select New Stored Procedure. Sql Server opens a new query window with a default stored procedure creation template like below.

 

 

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:       

-- Create date:

-- 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

 

Let us understand this template.

This template contains parameters for parameter names, procedure name, author name, create date, and so on. These template parameters are in the format <parameter, type, value>:

a) PARAMETER_NAME: It is the name of the template parameter in the script.

b) DATA_TYPE: It is the optional data type of the template parameter.

c) VALUE: It is the default value to be used to replace every occurrence of the template parameter in the script.

d) SET NOCOUNT ON:

            1) It gives performance. When SET NOCOUNT is ON, the count (indicating the number of rows         affected by a Transact-SQL statement) is not returned.

            2) When SET NOCOUNT is OFF, the count is returned. It eliminates the sending of     ONE_IN_PROC messages to the client for each statement in a stored procedure.

            3) For stored procedures that             contain several statements that do not return much actual    data, this can provide a significant performance boost because network traffic is greatly          reduced. The setting of SET NOCOUNT is set at execute or run time and not at parse time.

 

e) RETURN:

            1) Return values indicate a return code from the stored procedure. The return value does not         have to be specified as the parameters do. We simply use the RETURN SQL statement to          return a value. This value has to be an Integer data type and can return any value you need.       For example, this value can be a return code, the number of rows affected by a SQL        statement, or the number of rows in a table. Basically any integer data that you want to             return can be specified in a return value in your stored procedure.

 

            2) The RETURN statement exits unconditionally from a stored procedure, so the statements           following RETURN are not executed. Though the RETURN statement is generally used for error            checking, you can use this statement to return an integer value for any other reason. Using       RETURN statement can boost performance because SQL Server will not create a recordset.

 

Ok, so let us create a stored procedure using above template

 

A) Stored procedure with no input parameters.

(Using SSMS create new stored procedure and click CTRL + SHIFT + M, this will open a box to comfortable provide parameter values)

 

 

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Vishal Nayan

-- Create date: 28-04-11

-- Description:   Our first Sp

-- =============================================

CREATE PROCEDURE Author_Titles

     

AS

BEGIN

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

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      BEGIN

      SELECT a.au_lname, a.au_fname, t.title

      FROM titles t

      INNER JOIN titleauthor ta ON t.title_id = ta.title_id

      RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id

      RETURN 0         

      END

END

GO

 

To execute it type below statement on query window

EXEC Author_Titles

 

You can also run stored procedure by selecting the sp and click Execute Stored procedure. A window will open , since our above Stored procedure do not tave any input parameter, so just clcik ok. A new query window will open with below statements;

 

USE [pubs]

GO

DECLARE     @return_value int

EXEC  @return_value = [dbo].[Author_Titles]

SELECT      'Return Value' = @return_value

GO

 

See the result below;

Ok, the query result is fine, but what return value is 0? Well even though we remove RETURN 0 statement from stored procedure, the result will be same.

 

Reason:

1) When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

2) One could say that no RETURN = RETURN NULL = RETURN 0. But no warning is issued because you have not run RETURN NULL. And zero is expected because it's a stored procedure.

Ok, the above stored procedure can also be written in better way. Below stored procedure check whether any previous stored procedure with same name exists or not. If yes, we drop and create new.

USE [pubs]

GO

 

/****** Object:  StoredProcedure [dbo].[Author_Titles]    Script Date: 04/29/2011 00:30:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Vishal Nayan

-- Create date: 28-04-11

-- Description:   Our first Sp

-- =============================================

 

IF EXISTS (SELECT * FROM sys.procedures WHERE SCHEMA_ID=SCHEMA_ID('dbo')

AND name= N'Author_Titles')

DROP PROCEDURE dbo.Author_Titles

 

GO

CREATE PROCEDURE [dbo].[Author_Titles]

     

AS

BEGIN

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

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      BEGIN

      SELECT a.au_lname, a.au_fname, t.title

      FROM titles t

      INNER JOIN titleauthor ta ON t.title_id = ta.title_id

      RIGHT OUTER JOIN authors a ON ta


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend