How to Create a Stored Procedure?

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.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: