Inserting values from generated @local_variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting values from generated @local_variables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-11 : 07:35:08

Steve writes "Hello,

At present I'm trying to perform the following:

1. Create a temp table
2. Create a while clause for a variable number of loops
3. At each loop, insert particular values into the temp table.

Step 1 and Two are fine, it's 3 that is the problem.

I need to perform something like the following:



DECLARE 
	@Counter [tinyint],
	@To1 [int],
	@Memo1 [varchar] (250),
	@To2 [int],
	@Memo2 [varchar] (250),
	@To3 [int],
	@Memo3 [varchar] (250)
SET @Counter = 1
SET @To1 = 1000
SET @Memo1 = 'TEST 01'
SET @To2 = 1002
SET @Memo2 = 'TEST 02'
SET @To3 = 1032
SET @Memo3 = 'TEST 32'


WHILE (@Counter <= 3)
BEGIN


INSERT INTO #TempTable (To, Memo)
VALUES (@To + @Counter, @Memo + @Counter)


SET @Counter = @Counter + 1
END

I'm not at all sure if this is even possible, as you can see what I'm attempting to do is call a dynamic variable "@To + @Counter, which I'm hoping could somehow be resolved into @To1 etc'.

Thanks in advance!
-Steve"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-11 : 08:19:20

What error message do you get?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-11 : 14:18:38

The code below will work. You should really not use Access or whatever tool you used to produce your SQL Code. It does a horrible job formatting things, and is not ANSI-92 compatible..

CREATE TABLE #TempTable(
[To] INT,
Memo VARCHAR(250))

DECLARE
@Counter [tinyint],
@To int,
@Memo varchar(250),
@To1 [int],
@Memo1 [varchar] (250),
@To2 [int],
@Memo2 [varchar] (250),
@To3 [int],
@Memo3 [varchar] (250)

SET
@Counter = 1
SET @To1 = 1000
SET @Memo1 = 'TEST 01'
SET @To2 = 1002SET @Memo2 = 'TEST 02'
SET @To3 = 1032SET @Memo3 = 'TEST 32'
WHILE (@Counter <= 3)
BEGIN
INSERT INTO #TempTable ([To], Memo)
VALUES (@To + @Counter, @Memo + @Counter)
SET @Counter = @Counter + 1
END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources