Introduction to stored procedures with Input and Output Parameters

 

CREATE proc proc_InAndOut
(
@ OutParam int output,
@ InParam nvarchar (50)
)
As
If exists (select * from Student where StudentName = @ inParam)
Begin
Set @ outParam = 1;
End
Else
Begin
Set @ outParam = 0;
End
GO

The stored procedure of the returned data table with input parameters

CREATE proc proc_getDataReader
(
@ StudentName nvarchar (50 ),
@ InParam nvarchar (50)
)
As
If exists (select * from Student where StudentName = @ inParam)
Begin
Select * from Student
End
Else
Begin
Select StudentName from Student
End
GO

Execute the stored procedure with output parameters in the query Analyzer

-- Declare @ inParam nvarchar (50) -- input parameters do not need to be defined
Declare @ outParam int -- the output parameter must be defined.
-- The Parameter order here must be consistent with the parameter order of the stored procedure.
Exec proc_InAndOut @ outParam output, @ inParam = 'tree'
Select @ outParam

The above code can be understood and implemented as follows:

Declare @ str int -- the output parameter must be defined.
-- The Parameter order here must be consistent with the parameter order of the stored procedure.
Exec proc_InAndOut @ outParam = @ str output, @ inParam = 'tree'
-- Or abbreviated
Exec proc_InAndOut @ str output, @ inParam = 'tree'
-- Or abbreviated
Exec proc_InAndOut @ str output, 'tree'
 
Select @ str

Run the stored procedure with input parameters in the query analyzer to return data rows.

Exec proc_getDataReader @ StudentName = 'jianbao', @ inParam = 'tree'
-- Can also be abbreviated as follows
Exec proc_getDataReader 'jianbao', 'tree'