There are mainly two ways by which you can store a batch of statements in SQL Server, Procedures and functions. Both are almost similar in structure, with few differences. In this section, I am mainly concentrating on Stored procedures.
So, what is a Stored Procedure ? It's nothing but a batch of stored SQL Statements, which can accepts and returns values if needed.
Syntax
1: CREATE PROC[EDURE] urProcedureName
2: [<@InputParameter> Datatype default] [, 1..n] ]
3: [<@OutputParameter> Datatype OUT[PUT]][, 1..n] ]
4: [WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
5: AS
6: [BEGIN ]
7: [SqlStatements;][1..n]
8: [END]
9: GO
Parameter description
urProcedureName : this is the desired name of the new stored procedure, it can be upto 116 characters. Because of some performance problems, it is not recommended to prefix "sp_" in user defined procedures.
parameters : these holds the value, you passed to the procedure. During the execution, we need to provide the value of each input parameters, unless it has been defined with some default value.A stored procedure can have a maximum of 2,100 parameters. By default, parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects.
The maximum size of a stored procedure is 128 MB.
At this moment, I think this is sufficient for you to start with SQL Server stored procedures. for detailed information on the other parameters check books online.
My first stored procedure
Once you have knows the sql syntax, it is pretty easy to create stored procedures. As a simple example here we are going to create a stored procedure that just returns a string.
1: CREATE PROCEDURE MyfirstStoredProc
2: AS
3: SELECT 'Welcome to the world of Sql Server'
4: GO
5:
Type the above code in "Query analyzer" if you are using sql server 2000, or in "SQL Server Management studio" if you are using sql server 2005.In order to compile it, press "F5", if you wont get any error messages, then your statements will get stored in the database, and inorder to further modify it, you can either DROP the procedure and recreate it, or you can replace "CREATE" with "ALTER".
The above example creates a procedure with name "MyFirstStoredproc" which shows the message we mentioned in line # 3. The "GO" statement on line#4, indicates the end of the batch (and thus the end of the procedure).
In order to execute the procedure, you need to use "EXEC " followed by the procedure name.
EXEC MyfirstStoredProc
Stored Procedure with input parameters
Now we are going to write a stored procedure to do some addition operation, this stored procedure accepts two numbers and shows the result.
1: CREATE PROCEDURE retSum
2: @Num1 INT,
3: @num2 INT
4: AS
5: SELECT (@Num1 + @Num2) AS Total
6: GOthe above stored procedure has two input parameters, @num1 and @num2, declared as integer.
You can execute the parameterized procedures in 2 ways, the first one is the based on 'Relative location' ( values get substituted implicitly based one position) and the second one is the 'Explicit' substitution.
EXEC retSum 10, 20 -- implicit substitution
EXEC retSum @num2 = 20,
@Num1 = 10 -- Explicit substitution
For explicit substitution, you must know the parameters of the sp. for example, if you accidenlty types @num4 for an existing parameter, then the sp call will throw an error like
Server: Msg 8145, Level 16, State 2, Procedure retSum, Line 0
@num4 is not a parameter for procedure retSum.
So you must be very careful, while using the explicit call.
Stored Procedure with Output parameter
You can return values from a stored procedure in three different ways.
- Using SELECT statement
- Using RETURN statement
- Using OUTPUT parameters
The above examples uses the 'SELECT' statement to return a the value. In this section, we are going to look at the other two.
CREATE PROCEDURE retSumAsOutput
@Num1 INT,
@num2 INT,
@Sum INT OUT
AS
SELECT @sum = @Num1 + @Num2
GO
Executing this sort of stored procedure is somewhat tricky, you need to declare the variables to hold the values returned by the stored procedure.For example, above procedure can be executed as follows
DECLARE @Res INT
EXEC retSumAsOutput 1,2, @Res OUT
SELECT @Res
Returning a value from SQL Server Stored procedure using Return Statement
Return statement can return only an integer value. Also you should note that, the execution quits unconditionally once it processes the 'Return' statement, so the statements after the 'Return' wont get executed.
CREATE PROCEDURE retTest
@i int
as
RETURN @i*@i
GO
DECLARE @retValFromSP int
EXEC @retValFromSP = retTest 10
SELECT @retValFromSP
I hope, all of you got some basic idea on writing the stored procedures. Now, let me explain what are the advantages of Stored procedures over the usual sql query.
The main advantage is the performance. Whenever you submits a query to SQL, it has to go several processes before execution( Parsing -> optimization ->Compilation -> execution), and for the Stored procedures this is an one time process and the SQL stores the execution plan in the cache , and the subsequent calls to this stored procedure will make of this saved plan, thus saving a lot of time.
Using SQL server stored procedures, you can avoid the network round trips. Consider the situations where you are sending a long sql statement from the front end and imagine the same page will get called from the application more that 100 times per hour.Now if you have a stored procedure, which does the same stuff, can you say how much bandwidth we saved ?
Another advantage is that a procedure encapsulates the business logic. for example, if I run an sql server stored procedure using 'exec tstSp' , then no one in this world can tell, what is happening inside that procedure, without looking the script.
Security, the SQL server stored procedures provides security in two was; the first one is by using 'ENCRYPTION' option it can encrypts the contents of that stored procedure, this prevents the users from directly viewing the contents of the sp and hides the business logic. We don't actually need to give permissions to those tables used in that particular stored procedure, we just need to provide the users to execute that stored procedure.
If you write the Queries at the front end itself, then future modifications, need a more time for deployment.Now, if you implement the logic at the back end, its pretty easy to manage .So you can save lot of time for deployment and compilation.
Reusability; if you encapsulate the sql server statement as a stored procedure, you just need to call that stored proc, wherever you need to execute that statement.
