Local variable limitations / Prove

Posted by Sarvesh under Sql Server on 12/7/2009 | Views : 1391 | Status : [Member] | Replies : 3
Hi Experts

Could you please tell me,

1. Is there any maximum limitations are there for Local variables ?
2. If so, How can I prove It ?

Thanks is advance
Sarvesh S




Responses

Posted by: Abhi2434 on: 12/7/2009 [Member] [Microsoft_MVP] [MVP] Silver

Up
0
Down
I dont think there is any.... If so, it would be so large numbers, which would not cause any problems.

Also it depends on the memory the server has for this.

:)

www.abhisheksur.com

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 12/7/2009 [Member] [MVP] Silver

Up
0
Down
Hi

1. YES. You can declare only 10000 (Ten Thousand) Local variables within a Scope.

2. You can Prove.... Try...

DECLARE @nvarDynamic NVARCHAR(MAX),
@intSequence INT

SELECT @nvarDynamic = N'DECLARE ',
@intSequence = 1

WHILE(@intSequence <= 10000)
BEGIN
SELECT @nvarDynamic = @nvarDynamic + N'@COL' + CAST(@intSequence AS VARCHAR) + ' INT,'
SELECT @intSequence = @intSequence + 1
END

SELECT @nvarDynamic = LEFT(@nvarDynamic,LEN(@nvarDynamic)-1)

EXEC(@nvarDynamic)

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kumarsudu on: 12/7/2009 [Member] Starter

Up
0
Down
Hi Pandian,

Thanks for the answer and you are good in sql.

Thanks and Regards,
Kumar

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response