SQL Stored Procedure

The stored procedure is method where we use SQL code which can be saved, so that the code can be reutilised over and over again.

Suppose if we have a SQL query that we want to write it over and over again, we can save it as the stored procedure, and then we can just write the code to execute it. We can also input the variable to the stored methods so that the stored methods will act based on the parameter values that are passed.

Here is the syntax:

CREATE PROCEDURE procedure name
AS
sql_statement
GO;

To execute the statement we need to write:

EXEC procedure name;

Here is the Structured Query Language statement which will generate the stored procedure and is named as the “SelectAllCustomers”. It selects all the records from the table: “Customers” table:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

And to execute the statement we need to type:

EXEC SelectAllCustomers;

Here is the SQL declaration which will generate the stored procedure to select the Customers from a particular City from the “Customers” table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(50)
AS
SELECT * FROM the Customers WHERE City = Cityname;
GO;

Execute the stored procedure above as follows:

Lets see an example:

EXEC SelectAllCustomers @City = ‘London’;

Stored procedure with the multiple parameters:

Setting up different parameters is very easy. We need to list each parameter and its data type separated by a comma as shown below.

Here is the Structured Query Language declaration which will cause the stored procedure to choose the Customers from the specific City with a particular Postal Code from the “Customers” table: here is an example:

Lets see an example:

• CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(60)
  AS
  SELECT * FROM Customers WHERE City = Cityname AND PostalCode = PostalCode
  GO;

Execute the stored procedure above as follows:

Lets see an example:

• EXEC SelectAllCustomers @City = ‘London’, @Postal Code = ‘12209’;

SQL comments

As we know that the remark are being used to explain in the sections of Structure Query Language statements as well as to stop it from the execution of SQL statements.

Comments are not being supported in the Microsoft access of the databases

There are different types of comments.

• Single line comments

Single line comment here the sql server will avoid the text which has been written after the dashes in the single line which is called as commenting out. We can see it in the various colours in the sql server management studio once it has been commented on the program

If there is any text between — and the end of the line will be ignored this will not be executed

Here is an example, that uses a single-line comment as an explanation:

Example: 

– – Select all:
SELECT * FROM Customers;

Here is the example which will uses a single-line comment to be ignore the end of a line:

• SELECT * FROM the Customers  WHERE City=‘Berlin’; – – Select all:

• Multiline comments

Here is the another type of comments which is multiline comments

If we require comment of multiple lines we need to put double dash on each line and its not real solution if we want to comment multiple lines of programs then the sql server will make use of the multi line comments

Multi-line comments which starts with /* and end with */.

If there is any text between /* and */ will be ignored.

Here is the example that uses a multi-line comment

• /*Select all the columns
  of all the records
  in the Customers table:*/
  SELECT * FROM Customers;

Here is the example which uses a multi-line comment to ignore many statements:

/*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;