.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to Create a Stored Procedure ?

Posted By:Shashi Ray       Posted Date: September 21, 2008    Points: 15    Category: DataBase    URL: http://www.dotnetspark.com  


How to Create a Stored Procedure ?

When the CREATE PROCEDURE statement is executed, the syntax of the SQL statements within the procedure is checked. If you have made a coding error the system responds with an appropriate message and the procedure is not created.

The Syntax of the CREATE PROCEDURE statement

AS sql_statements

You can use CREATE PROCEDURE statement to create a stored procedure in the database. The name of the stored procedure can be up to 128 characters and is typically prefixed with the letters sp.
If you look at the above options like AS, RECOMPILE, ENCRYPTION these are having some significance meaning to it.
The AS clause contains the SQL statements to be executed by the stored procedure. Since a stored procedure must consist of single batch.

Recompile is used when you want to compile the stored procedure every time when you call. This comes into the picture when one doesn't want to catch the execution plan of stored procedure in the database. Encryption implies that you want to hide this code so that no one can see it. This is very important when you want to distribute the code across the globe or when you want to sell this code to other vendors. But make sure you have original copy it; because once you encrypted it no one can decrypt it.

Apart from the stored procedure that store in the database a permanent entity you can create stored procedure as per you session. That means as long the as the session is alive then the stored procedure is available in the memory means in the database.
Once the session ends the stored procedure is vanished this actually depends on what type of stored procedure you have chosen to create it.

Stored procedure provide for two different types of parameters: input parameters and Output Parameters. An input Parameter is passed to the stored procedure from the calling program. An output parameter is returned to the calling program from the stored procedure. You can identify an output parameter with the OUTPUT keyword. If this keyword is omitted the parameter is assumed to be an input parameter.

You can declare an input parameter so that it requires a value or so that its value is optional. The value of a required parameter must be passed to the stored procedure from the calling program on an error occurs. The value of an optional parameter doesn't need to be passed from the calling program. You identify an optional parameter by assigning a default value to it. Then if a value isn't passed from the calling program, the default value is used. You can also use output parameter as input parameters. That is you can pass a value from the calling program to the stored procedure through an output parameter. However is not advisable to pass parameters to Output parameters.

The syntax for declaring the parameters

@Parameter_name_1 data_type [= default] [OUTPUT]
[, @Parameter_name_2 data_type [= default] [OUTPUT].

Parameter declarations
@FirstName varchar(50) -- Input parameter that accepts a string.
@LastName varchar(50) -- Output Parameter that returns a string.
Create Procedure statement that uses an input and an output parameter.

                       @FirstName varchar(50),
                       @LastName varchar(50)
SELECT  @LastName= ln_Name 
WHERE   fn_name = @FirstName

Create procedure statement that uses an optional parameter.


                       @LastName varchar(50),
                       @FirstName varchar(50) = 'shashi'
SELECT  @LastName= ln_Name 
WHERE   fn_name = @FirstName

A stored procedure can declare up to 2100 parameters. If you declare two or more parameters, the declarations must be separated by commas.

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend