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.

 

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

 

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

 

You have included a good example of using arguments with Stored Procedures. Good one. This will help many programmers.

 

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

 

Your article is very helpful for me. Thanks for your great contribution.

 

hi friends,

i have doubt can we alter the stored procedure