Create Table Test (ID int primary key, name char (10 ))
Insert into test values (1, 'test1 ')
Insert into test values (2, 'test2 ')
Insert into test values (3, 'test3 ')
Insert into test values (4, 'test4 ')
1. Returned result set
Create procedure return_result
As
Select * from test
Exec return_result
-- Call
-- ID name
-- 1 test1
-- 2 Test2
-- 3 test3
-- 4 test4
2. Input and Output Parameters
Create procedure input_output
@ ID int,
@ Name char (10) Output
As
Begin
Select @ name = Name from test where id = @ ID
End
-- Call
Declare @ name char (10)
Exec input_output 1, @ name output
Select @ name
-- Output result
-- Name
-- Test1
3. Return Value
-- Each stored procedure can return an integer value with a return statement.
Alter procedure return_value
@ ID int
As
Begin
Declare @ count int -- only integer values can be returned
Select @ COUNT = count (*) from test where id = @ ID
Return @ count
End
-- Call
Declare @ count char (10)
Exec @ COUNT = return_value 1
Select @ count shuliang
-- Structure
-- Shuliang
-- 1
