Variables in Transact-SQL Procedures

SQL Anywhere and Sybase IQ assign values to variables in procedures differently than Transact-SQL.

SQL Anywhere and Sybase 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:

Related concepts
Criteria for Writing Compatible Queries