EXECUTE (Microsoft SQL Server)

Syntax

EXECUTE [-XC][ON-ERROR=procedure[(arg1[,argi]...)]] [DO=procedure[(arg1[,argi]...)]] [@#status_var=]stored_procedure_name [[@param=]{any_col|_var|_lit}[,...]] [INTO any_coldata_type[(length_int_lit)] [,...]][WITH RECOMPILE]

Description

Runs a stored procedure in Microsoft SQL Server database.

If the stored procedure specified in stored_procedure_name contains a SELECT query, the EXECUTE command must specify an INTO argument to process the values from the query. If no INTO argument is specified, then the values from the query are ignored.

EXECUTE retrieves just the first row when the following conditions are met:

  • The DO procedure is not specified.

  • The stored procedure, stored_procedure_name, selects one or more rows.

  • An INTO argument is specified.

This is useful for queries returning a single row.

Parameters

Parameter Description

ON-ERROR

Declares an SQR procedure to run if an error occurs. If ON-ERROR is omitted and an error occurs, SQR halts with an error message. For severe errors (for example, passing too few arguments) SQR halts, even if an error procedure is specified.

You can specify arguments to be passed to the ON-ERROR procedure. Arguments can be any variable, column, or literal.

DO

Specifies an SQR procedure to run for each row selected in the query. Processing continues until all rows have been retrieved. You can specify arguments to be passed to the procedure. Arguments can be any variable, column, or literal.

@#status_variable

Returns the procedure status in the specified numeric variable. The status is returned only after selected rows are retrieved.

stored_procedure_name

Names the stored procedure to run.

@param

Names the parameter to pass to the stored procedure. Parameters can be passed with or without names. If used without names, they must be listed in the same sequence as defined in the stored procedure.

any_lit|_var|_col

Specifies the value passed to the stored procedure. It can be a string, numeric, or date variable, a previously selected column, a numeric literal, or a string literal.

OUT[PUT]

Indicates that the parameter receives a value from the stored procedure. The parameter must be a string, numeric, or date SQR variable. Output parameters receive their values only after rows selected have been retrieved. If you specify multiple output parameters, they must be in the same sequence as defined in the stored procedure.

INTO

Indicates where to store rows that are retrieved from the stored procedure's SELECT statement. The INTO argument contains the names of the columns with data types and lengths (if needed). You must specify the columns in the same sequence and match the data type used in the stored procedure's SELECT statement.

If the stored procedure contains more than one SELECT query, only the first query is described with the INTO argument. Rows from subsequent queries are ignored.

WITH RECOMPILE

Causes the query to recompile each time it is run rather than using the plan stored with the procedure. Normally, this is not required or recommended.

Example

The following code example invokes the stored procedure get_total with two parameters: a string literal and a string variable. The result from the stored procedure is stored in the variable #total. 

execute get_total 'S. Q. Reporter' $State #Total Output

The following code example invokes the stored procedure get_products with two parameters. The stored procedure selects data into five column variables. The SQR procedure print_products is called for each row retrieved. The return status from the stored procedure is placed in the variable #proc_return_status. 

execute do=print_products
   @#proc_return_status=
   get_products
   @prodcode=&code,            @max=#maximum
   INTO &prod_code         int,
      &description         char (45),
      &discount         float,
      &restock         char,
      &expire_date         datetime

begin-procedure print_products
print &prod_code            (+1,1)
print &description            (+5,45)
print &discount            (+5) edit 99.99
print &restock            (+5) match Y 0 5 Yes N 0 5 No
print &expire_date            (+5,) edit 'Month dd, yyyy'
end-procedure