INF: Limit of 40 Variables with DECLARE in Batch and Procedure

ID Number: Q75705

1.10 1.11 4.20

OS/2

docerr

Summary:

Page 93 of the "Microsoft SQL Server Language Reference" for version

1.1 incorrectly states that up to 255 parameters are allowed in a

procedure, and an unlimited number of local variables are allowed.

In SQL Server versions 1.1 and 1.11, there is a limit of 40 local

variables for batches and stored procedures.

More Information:

Version 1.1x Servers

--------------------

In a procedure, 40 parameters are allowed to be passed in, in addition

to DECLAREing 40 local variables. In a batch, only 40 variables are

allowed. This limit will be achieved by issuing a single DECLARE

statement or a combination of multiple DECLARE statements as

illustrated in the SQL fragments below:

DECLARE @v001 int

<rows deleted>

,@v040 int

declare @v001 int

<rows deleted>

declare @v040 int

go

Version 4.2 Servers

-------------------

The number each of stored procedure parameters and global variables is

255; local variable number is limited only by available memory.

Additional reference words: 1.10 1.11 4.20