Sunny Books
What we have

SQL Server stored procedure

A stored procedure is a group of SQL (Transact SQL) statements. If you have a situation where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by its name.

Create stored procedure

CREATE PROCEDURE spEmployeeSimplified
AS
BEGIN
	SELECT name, email, salary FROM em.employee
END

Execute stored procedure:

1. Call procedure name directly: spEmployeeSimplified

2. Exec: spEmployeeSimplified

3. Execute: spEmployeeSimplified

Result

Name Email Salary
Daviddavid@yahoo.com7000
Alicealice@hotmail.com7000
Roserose@yahoo.com7500
Jackjack@gmail.com7600
Tomtom@yahoo.com8000
Alexaalexa@gmail.com7300
Chantalchantal@gmail.com7600
Ellaella@hotmail.com7200
Kavenkaven@gmail.com8000
ErikNULL7800

Create stored procedure with parameters

CREATE PROC spEmployeeSimplified2
@department_id int, @salary int
AS
BEGIN
	SELECT name, email, salary, department_id FROM em.employee 
	WHERE department_id=@department_id AND salary>=@salary
END

//Call: 
spEmployeeSimplified2 1, 7500

Result

Name Email Salary Department id
Tomtom@yahoo.com80001
Chantalchantal@gmail.com76001

View the text

sp_helptext spEmployeeSimplified2
//result
CREATE PROC spEmployeeSimplified2
@department_id int, @salary int
AS
BEGIN
	SELECT name, email, salary, department_id FROM em.employee WHERE 
department_id=@department_id AND salary>=@salary
END

Alter procedure

ALTER PROC spEmployeeSimplified2
@department_id int, @salary int
AS
BEGIN
   SELECT name, email, salary, department_id FROM em.employee 
   WHERE department_id=@department_id AND salary>=@salary ORDER BY name
END

Drop procedure

DROP PROC spEmployeeSimplified2

Create stored procedure with output

CREATE PROCEDURE spEmployeeSimplified3
@gender nvarchar(50) , @employeeCount int output
AS
BEGIN
   SELECT @employeeCount = COUNT(employee_id) FROM em.employee
   WHERE gender=@gender
END

//Execute:
DECLARE @employeeTotal int
EXECUTE spEmployeeSimplified3 'male', @employeeTotal output
PRINT @employeeTotal

Result: 5

Note: if the OUTPUT (or OUT) keyword is not specified when executing the stored procedure, the return value will be NULL.

If the parameter name is explicitly specified, the order of parameters is changeable.

DECLARE @employeeTotal int
EXECUTE spEmployeeSimplified3 @employeeCount = @employeeTotal output, @gender = 'male'
PRINT @employeeTotal

Useful system stored procedures

1. Sp_help_procedure_name:view the information about the stored procedure, like parameter names, their datatypes, etc. it can be used with any database object like tables, views, sps, triggers, etc.

2. Sp_helptextprocedure_name:view the text of the stored procedure.

3. Sp_dependsprocedure_name: view the dependnecies of the stored procedur. The system sp is very useful especially if yo want to check if there are any stored procedures that are referencing a table that you are about to drop. It also can be used with other database objects like tables.

Return values of stored procedure

Whenever a stored procedure is executed, it returns an integer status variable. Usually, zero indicated success and non-zero indicates failure.

CREATE PROCEDURE spTotalEmployees
AS
BEGIN
	RETURN (SELECT COUNT(employee_id) FROM em.employee )
END

DECLARE @total int
EXECUTE @total=spTotalEmployees
PRINT @total

Output parameters vs. return values

Return status value output parameters
Only integer datatypeany datatype
Only one valueOnly one value
Used to convey success or failureused to return values like name, count etc.
SUNWEB EXPERT