Stored Procedure in sql server

venkatesanj@hcl.in's Avatar author of Stored Procedure in sql server
This is an article on Stored Procedure in sql server 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.
shabbir like this
0
trinitybrown's Avatar, Join Date: Oct 2008
Go4Expert Member
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
0
LenoxFinlay's Avatar
Banned
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...
0
technica's Avatar, Join Date: Dec 2007
CoderByNature
You have included a good example of using arguments with Stored Procedures. Good one. This will help many programmers.
0
LynxSI's Avatar
Go4Expert Member
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
0
virender.ets's Avatar
Banned
Your article is very helpful for me. Thanks for your great contribution.
0
anisa's Avatar, Join Date: Jul 2012
Newbie Member
hi friends,

i have doubt can we alter the stored procedure