The
name itself defining the behavior of “Stored Procedures” (Stored-Saved;
Procedure-Query)
For
example I want to get the Employee table information, for that I can fire a SELECT query
as
SELECT
* FROM Employee
Here
SELECT query is simple; if you require same table information for multiple times,
you can write and fire the same query multiple times & get the results. For suppose the
query has multiple lines like
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM Employee
WHERE
DEPTNO = 10
ORDER
BY SAL DESC
It is time consuming process to write the same queries for multiple times.
Instead of writing multiple times, I can store this query in database
and can use it for multiple times by calling to it (Avoid re-witting).
CREATE
PROCEDURE Proc_Employee
AS
SELECT * FROM Employee
Or
CREATE
PROCEDURE MaxSalEmp
AS
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM Employee
WHERE DEPTNO = 10
ORDER BY SAL DESC
Whenever you want to execute the query which has been stored,
just call the name of the place by EXEC
<PlaceName>.
EXEC MaxSalEmp
If you want to modify that query;
just write as
ALTER PROCEDURE MaxSalEmp
AS
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM Employee
WHERE DEPTNO = 20 -- Modified
ORDER BY SAL DESC
In the above example, if
you want to know the details of another department number instead of 10 or 20, need
to create another empty space (Parameter) in the Stored Procedure with a name and data type (@Parameter datatype). And pass the required values to that particular space at execution,
CREATE PROCEDURE
MaxSalEmp (@DNO INT) –- Parameter Declararion
AS
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM Employee
WHERE DEPTNO = @DNO -- Make it as Dynamic
ORDER BY SAL DESC
And
call the name of the place with parameter by EXEC
<PlaceName>(Parameter),
EXEC MaxSalEmp 10
-- Call the Stored Procedure with value
If you want to drop the place as DROP
PROCUDURE <PlaceName>
DROP PROCEDURE MaxSalEmp
No comments:
Post a Comment