Returns the data using the OUTPUT parameter If the parameter is specified for the parameter in the process definition, the stored procedure returns the current value of the parameter to the calling program when exiting (whether it is a bit like "according to address delivery"). To save the parameter value with a variable to use in the call program, the calling program must use the Output key when performing the stored procedure.
Example The following example displays a stored procedure with an input parameter and an output parameter. The first parameter @title during the stored procedure will receive the input value specified by the call program, while the second parameter @ytd_sales will return to the calling program. The SELECT statement uses the @TITLE parameter to get the correct YTD_sales value and give this value to the @ytd_sales output parameter.
Create Procedure get_sales_for_title @ title varchar (80), - this is the input parameter. @ YTD_SALES INT OUTPUT - this is the output parameter.as
- Get the sales for the specified title and - assign it to the output parameter.select @ytd_sales = ytd_salesfrom titleswhere title = @title
Returngo
The following programs perform stored procedures with input parameter values, and save the output value of the stored procedure to the local variable @ytd_sales_for_title of the calling program.
- Declare the variable to receive the output value of the procedure.declare @ytd_sales_for_title int
- Execute the procedure with a title_id value - and save the output value in a variable.
Execute get_sales_for_title "Sushi, Anyone?", @Ytd_sales = @ytd_sales_for_title output
- Display the value returned by the procedure.print 'Sales for "Sushi, Anyone?":' Convert (varchar (6), @ YTD_SALES_FOR_TITLE) GO
Sales for "Sushi, Anyone?": 4095
When performing a stored procedure, you can also specify the input value for the OUTPUT parameter. This will allow the stored procedure to receive a value from the calling program, change the value, or perform the operation on the value, and return the new value to the calling program. In the previous example, a value can be assigned to @ytd_sales_for_title variables before performing the stored procedure. The @ytd_sales variable contains the parameter value in the stored procedure body, and the stored procedure returns the @ytd_sales variable value to the calling program when exiting. This is often referred to as "inlet function".
If the parameter specifies Output when the stored procedure is executed, this parameter is not defined in the stored procedure, and an error message will be received. When performing a stored procedure with an OUTPUT parameter, Output may not be specified. This will not return an error, but it will not be able to use the output value in the call program.
(Q :) Is there any difference between RETURN and OUTPUT?
(A:) Return can only be one; Output can have multiple (Q :) have two processes! / * Procedure 1 Detect the user name to repeat * / create proc Check_user @name varchar (15), @State int out @State set @State = 1 END / * Procedure 2 want Call the @State value of Check_User, if not 1, write to the data table, but I don't know how to get this value. * / create proc insert_new @name varchar (15), @pw varchar (15), @Email varchar (20) as begin 'When @ State = 0 Insert Into ------- End
(A:) create proc check_user @name varchar (15), @ state int outputas if exists (select name form tblname where name = @ name) set @state = 1 else set @state = 0GO create proc insert_new @name varchar (15 ), @ pw varchar (15), @ Email varchar (20) asdeclare @State intexec check_user @ name, @ State Outputif@ State = 0 Insert Into ... Go
