To check the parameters of Stored Procedure

SELECT name
FROM sys.parameters
WHERE object_id = OBJECT_ID('YourProcedureName')

Get SQL Server schema via a SQL query?

 

SELECT * FROM INFORMATION_SCHEMA.TABLES;

SELECT * FROM INFORMATION_SCHEMA.VIEWS;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS ;

PL/SQL CURSORS

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc.
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:

• Implicit cursors
• Explicit cursors

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:

Attribute	Description
%FOUND	Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND	The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN	Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT	Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

Example:

We will be using the CUSTOMERS table we had created and used in the previous chapters.

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected:

DECLARE 
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF; 
END;
/

When the above code is executed at SQL prompt, it produces the following result:

6 customers selected

PL/SQL procedure successfully completed.

If you check the records in customers table, you will find that the rows have been updated:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |
|  2 | Khilan   |  25 | Delhi     |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2500.00 |
|  4 | Chaitali |  25 | Mumbai    |  7000.00 |
|  5 | Hardik   |  27 | Bhopal    |  9000.00 |
|  6 | Komal    |  22 | MP        |  5000.00 |
+----+----------+-----+-----------+----------+

Explicit Cursors

Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.
The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

• Declaring the cursor for initializing in the memory
• Opening the cursor for allocating memory
• Fetching the cursor for retrieving data
• Closing the cursor to release allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:

CURSOR c_customers IS
   SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:

OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:

CLOSE c_customers;

Example:

Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE
   c_id customers.id%type;
   c_name customers.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   OPEN c_customers;
   LOOP
      FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

1 Ramesh Ahmedabad 
2 Khilan Delhi 
3 kaushik Kota    
4 Chaitali Mumbai 
5 Hardik Bhopal  
6 Komal MP  

PL/SQL procedure successfully completed.

 Below is tested code in Microsft Sql Server 2014

Declare @id as int;
Declare @name as NVARCHAR(50);
Declare @age as NVARCHAR(50);
Declare @address as NVARCHAR(50);
Declare @salary as NVARCHAR(50);

Declare @c_customers as cursor
set @c_customers =cursor for
select id,name,age,[address],salary from customer;
open @c_customers;
fetch next from @c_customers into @id, @name,@age,@address,@salary;
while @@FETCH_STATUS=0
begin
Update customer set salary=salary+500 where id=@id
print cast(@id as varchar (50))+' '+@name;
fetch next from @c_customers into @id,@name,@age,@address,@salary;
end
close @c_customers;
deallocate @c_customers; 

--To check the updated Data
select * from customer

SQL JOINS EASY LEARNING

10 interesting facts about SQL for beginners

10 interesting facts about SQL for beginners

1. SQL stands for Structured Query Language. It is a database programming language that acts a means of communication between two other programs such as a website and a database.
2. SQL helps to structure and manage information in a database as well as carry out searches for information using structures. Some of these structures might include putting all the information about cars into tables comprising of makes, models, year of manufacture, and colour.
3. SQL makes an enquiry (or Query as the name suggests) to a database by sending a request. This request could come from a website such as Autotrader or a search engine.
4. SQL can perform all of these functions in a fraction of a second.
5. There are different developers of SQL, such as Oracle, Sybase, and Microsoft, however their SQL programs all fundamentally work the same way.
6. MySQL is an opensource version of SQL, meaning it is open to the public to upgrade through the internet and best of all… free. This makes it very popular with developers.
7. SQL falls into the middleware category of software. Middleware typically functions in between two other programs, such as a website (the front end) and a database (the backend) allowing them to communicate.
8. Three good examples of websites that use SQL in some capacity are Ebay, Autotrader, and Amazon.
9. The types of people that may use SQL include Applications Developers, Programmers, and Database Administrators (i.e. the people that look after large databases).
10. One type of commonly known database program that works well with SQL is ‘Microsoft Access’. This works with Microsoft’s own version of SQL (Microsoft SQL) which is typically stored on business servers (i.e. central computers). This type of set up allows thousands of employees to access data through their own PCs from the server. This data could be anything from financial information, customer details or retail goods that their company might sell.

SQL Hosting

SQL Hosting
If you want your web site to be able to store and display data from a database, your web server should have access to a database system that uses the SQL language.
If your web server will be hosted by an Internet Service Provider (ISP), you will have to look for SQL hosting plans.
The most common SQL hosting databases are MySQL, MS SQL Server, and MS Access.
You can have SQL databases on both Windows and Linux/UNIX operating systems.
Below is an overview of which database system that runs on which OS.
MS SQL Server
Runs only on Windows OS.
MySQL
Runs on Windows, Mac OS X, and Linux/UNIX operating systems.
MS Access (recommended only for small websites)
Runs only on Windows OS.
SQL Summary
This SQL tutorial has taught you the standard computer language for accessing and manipulating database systems.
You have learned how to execute queries, retrieve data, insert new records, delete records and update records in a database with SQL.
You have also learned how to create databases, tables, and indexes with SQL, and how to drop them.
You have learned the most important aggregate functions in SQL.
You now know that SQL is the standard language that works with all the well-known database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and MS Access.

The HAVING Clause

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value

________________________________________

SQL HAVING Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find if any of the customers have a total order of less than 2000.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000 br="br">The result-set will look like this:

Customer	SUM(OrderPrice)
Nilsen	1700

Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

Customer	SUM(OrderPrice)
Hansen	2000
Jensen	2000