Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/articles/sql-server/)
-   -   Stored Procedure in sql server (http://www.go4expert.com/articles/stored-procedure-sql-server-t9129/)

venkatesanj@hcl.in 4Mar2008 09:03

Stored Procedure in sql server
 

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.

trinitybrown 25Oct2008 12:44

Re: Stored Procedure in sql server
 
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

LenoxFinlay 7May2009 11:23

Re: Stored Procedure in sql server
 
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...

technica 24Dec2009 14:10

Re: Stored Procedure in sql server
 
You have included a good example of using arguments with Stored Procedures. Good one. This will help many programmers.

LynxSI 24May2010 05:06

Re: Stored Procedure in sql server
 
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

virender.ets 27Jan2011 16:59

Re: Stored Procedure in sql server
 
Your article is very helpful for me. Thanks for your great contribution.

anisa 24Jul2012 15:53

Re: Stored Procedure in sql server
 
hi friends,

i have doubt can we alter the stored procedure


All times are GMT +5.5. The time now is 07:54.