SQL Server 2008 Stored procedure parameters

--Using stored procedure parameters
--including input and output parameters, and default values for parameters
--Specifying parameter names and data types
--Input parameters allow the user to pass data values to a stored procedure or function
--output parameters allow the stored procedure to pass data values or cursor variables to the user

--The parameters of the stored procedure should be defined between create procedure and as key values at creation time, each parameter has a specified parameter name and data type, and the parameter name must be
[email protected] begins with a prefix, separated by commas between each parameter definition
[email protected]_name Data_type[=default][output]

--Example: Create a stored procedure that specifies a parameter name and data type
Use Student performance management system
Go
CREATE PROCEDURE Pro_ Teacher Information
@age int, @sex Cnhar (10)
As
Select teacher number, name, gender, age, birthplace
From teacher information
where age[email protected]and sex[email protected]

--sql server2008 provides two ways to pass parameters
--Pass by position

This is done in the statement that executes the stored procedure, giving the value of the parameter directly, and when there are multiple arguments, the order of the arguments is consistent with the order of the parameters in the statement that created the stored procedure

For example:
EXEC pro_ Teacher Information 26, ' Women '

--pass through the name of the parameter

This is done in the statement that executes the stored procedure, gives the parameter value in the form of parameter name = argument value, and the advantage of passing the parameter by parameter name is that
Parameters can be given in any order
For example:
EXEC pro_ Teacher Information @sex = ' female ', @age =26

--Input parameters
An input parameter is a condition that is set in a stored procedure that specifies a value for this condition when the stored procedure is executed, returns the appropriate information through the stored procedure, and uses the
Input parameters can find a database multiple times to the same stored procedure
Use Student performance management system
Go
CREATE PROCEDURE Pro_ class information
@ class number nvarchar (20)
As
Select S. School number, S. Name, S. gender, S. class, C. Class name
From student information S, class information C
where S. Class =c. class number and S. class [email protected] class number

--Specify default values for parameters
You can specify a default value for an optional parameter when defining a parameter, execute the stored procedure, and use the default value if no other value is specified
Use Student performance management system
Go
CREATE PROCEDURE Pro_ Score information
@ score nvarchar (50) = ' 60 '
As
Select S., S. Name, S. gender, S. class, G. Grade number, G. Exam number, G. Course number, G. Score
From student information S, score information G
where S. Study number =g. Student number and G. Score >@ Score

EXEC PRO_ Score Information

--Output parameters
Use Student performance management system
Go
CREATE PROCEDURE Pro_teachername
@ Student Name nvarchar (+) = ' Hephec '
@ Instructor Name nvarchar () output
As
SELECT @ Instructor Name =t. Name
From student information S, class information C, counselor information T
where S. Class =c. class information and C. Counselor =t. Counselor Number
and S. Name [email protected] Student name
Go
In order to accept the return value of a stored procedure, you need to define a variable to hold the value of the returned parameter, in the call statement for the stored procedure, you must add output to the defined variable
Keyword to declare
declare @name nvarchar (20)
exec pro_teachername ' hephec ' @Name output
Select ' Counselors are: ' + @name as ' results '
Go

SQL Server 2008 Stored procedure parameters