Stored Procedure in sql server

Discussion in 'SQL Server' started by venkatesanj@hcl.in, Mar 4, 2008.

  1. venkatesanj@hcl.in

    venkatesanj@hcl.in New Member

    Joined:
    Oct 19, 2007
    Messages:
    24
    Likes Received:
    1
    Trophy Points:
    0

    Introduction



    Stored procedures are special objects available in sql server. Its a precompiled statements where all the preliminary parsing operations are performed and the statements are ready for execution.

    Its very fast when compared to ordinary sql statements where the sql statements will undergone a sequence of steps to fetch the data

    Stored procedure involves various syntax based on the parameters passed. Let me show you a generic syntax for a stored procedure.

    Code:
     Create procedure procName
     as
     begin
       ----Your query should be return here
     end
    
    To execute the stored procedure we have to use exec command,
    Code:
    exec procName
    
    Let me show you a simple example where i am trying to pass two parameters and retrieving one parameter as output.
    Code:
    CREATE PROCEDURE SPWITHPARAMETER_AND_RETURN_VALUE
      (
        @EMPID INT,
        @IDVAL INT,   
        @RETURNVALUE INT =0 OUT
      )
      AS 
    	DECLARE @COUNT INT
      BEGIN
      SELECT @COUNT=COUNT(*) FROM JOINTABLE WHERE EMPID=@EMPID AND IDVAL=@IDVAL
    	IF(@COUNT >0)
    	  BEGIN
      	
    	       SET @RETURNVALUE = 1;
                                 PRINT @RETURNVALUE    
    	        RETURN @RETURNVALUE
    		 	  END
             ELSE
    	   BEGIN
    	        SET @RETURNVALUE = 1;
                                  PRINT @RETURNVALUE
    	        RETURN @RETURNVALUE
                            END
      END
    
    In the above stored procedure the count of rows for empid and rowid matching in the table. Based on the count i am trying to set the return value. To execute the above stored procedure we have to use the below syntax,

    Code:
      EXEC SPWITHPARAMETER_AND_RETURN_VALUE 2,3
    
    Please provide me your valuable feedback regarding this article.
     
    Last edited: Mar 12, 2008
    shabbir likes this.
  2. trinitybrown

    trinitybrown New Member

    Joined:
    Oct 23, 2008
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    UK
    Ok I got it , but i wish to ask one thing that stored procedures works in the same way in every database or there are some differences, like oracle and MySQL, are stored procedures in the same way here also
     
  3. LenoxFinlay

    LenoxFinlay Banned

    Joined:
    Apr 15, 2009
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    0
    Hi,

    Here is the Microsoft Developer center for SQL Server:
    msdn2.microsoft.com/en-us/sql/def
    Not necessarily a set of tutorials, but lots and lots of references that might help get you started.
    Here is a tutorial site:
    functionx.com/sqlserver/index...
     
  4. technica

    technica New Member

    Joined:
    Dec 15, 2007
    Messages:
    107
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.technicaltalk.net
    You have included a good example of using arguments with Stored Procedures. Good one. This will help many programmers.
     
  5. LynxSI

    LynxSI New Member

    Joined:
    May 23, 2010
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Developer
    Location:
    Vancouver, BC, Canada
    Home Page:
    http://www.lynxsi.ca/
    In addition to the execution speed benefits using SQL Stored Procedures also provide an excellent mechanism for controlling database security. If you client/server apps use stored procedures for all database interactions (Select, Insert, Update, Delete) you do not need to give any normal end-users table level permissions.

    This helps increase security as well as data integrity. I highly recommend to any client/server developers that you make use of the benefits provided by SQL Stored Procedures.

    - Ben
     
  6. virender.ets

    virender.ets Banned

    Joined:
    Jan 22, 2011
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.rajasthancityguide.com/
    Your article is very helpful for me. Thanks for your great contribution.
     
  7. anisa

    anisa New Member

    Joined:
    Jul 24, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    hi friends,

    i have doubt can we alter the stored procedure
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice