Variables in Transact-SQL procedures

Adaptive Server Anywhere and IQ use the SET statement to assign values to variables in a procedure. In Transact-SQL, values are assigned using the SELECT statement with an empty table-list. The following simple procedure illustrates how the Transact-SQL syntax works:

CREATE PROCEDURE multiply 
					@mult1 int,
					@mult2 int,
					@result int output 
AS 
SELECT @result = @mult1 * @mult2

This procedure can be called as follows:

CREATE VARIABLE @product int
go
EXECUTE multiply 5, 6, @product OUTPUT
go

The variable @product has a value of 30 after the procedure executes.

There are some differences in order and persistence of variable declarations:

• In Adaptive Server Enterprise, you can declare variables anywhere in the body of a stored procedure. Variables persist for the duration of the procedure.

• In Adaptive Server Anywhere and IQ, you must declare variables at the beginning of a compound statement (that is, immediately after BEGIN in a BEGIN...END pair). Variables persist only for the duration of the compound statement.