having a changing variable ?????

[mikeyd]

Hi everyone,

Having serious problem trying to make a variable change.

this is in a loop
Set @versionSection = '@version'+@iCount

so theoratically @versionSection will be changed to eg. @version1 ????

Query is below the stars.

TA in advance

***********************************************************

CREATE PROCEDURE sp1_Insert
(
@iErrorCode int OUTPUT,
@number varchar(50),
@title varchar(300),
--@implementationdate datetime,
@implementationtype varchar(50),
@status bit,
@version1 decimal(9),
@version2 decimal(9),
@version3 decimal(9),
@version4 decimal(9),
@version5 decimal(9),
@version6 decimal(9),
@version7 decimal(9),
@version8 decimal(9),
@version9 decimal(9),
@version10 decimal(9),
@version11 decimal(9),
@version12 decimal(9),
@version13 decimal(9),
@version14 decimal(9),
@version15 decimal(9),
@version16 decimal(9),
@version17 decimal(9),
@version18 decimal(9),
@version19 decimal(9),
@version20 decimal(9),
@version21 decimal(9),
@version22 decimal(9),
@version23 decimal(9),
@version24 decimal(9),
@version25 decimal(9),
@version26 decimal(9),
@version27 decimal(9)
--@version28 decimal(9)
)
AS

-- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** --
INSERT into modification
( number,
title,
--implementationdate,
implementationtype,
status
)
VALUES
( @number,
@title,
--@implementationdate,
@implementationtype,
@status
)

-- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** --
Declare @iCount int, @modificationID int, @versionSection varchar(15)
Set @iCount = 1
Set @modificationID = @@IDENTITY

EXEC sp1_modification_section_addmodification @modification_id_1 = @modificationID

WHILE @iCount < 28

BEGIN

Set @versionSection = '@version'+@iCount

INSERT into modification_section_lnk
( modification_id,
section_id,
version
)
VALUES
( @modificationID,
@iCount,
@versionSection
)


print @iCount
set @iCount = @iCount + 1

END

-- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** --

SELECT @iErrorCode=@@ERROR
GO

 

[dmhirsch]

Not possible, unfortunately - there isn't an eval functionality to generate code on the fly. If you really have to do this, you will need to write out the 28 repeats of the code.

 

[sunila7]

Hi,

I think u must be encountering problem on this line....

Set @versionSection = '@version'+@iCount

Change it to

Set @versionSection = '@version'+CAST(@iCount as varchar)


Hope this helps

Sunil

 

[dmhirsch]

Sunil - I think she/he is attempting to create a variable name on-the-fly, so that @versionSection will be evaluated first to @version1, then @version2, etc. This can't work, right?

 

[mikeyd]

dmhirsch
Thats exactly what I am trying to do and its still not working. SQL is so limited

 

[sunila7]

dmhirsch- Missed that one!. But I think this code can be replaced like this..

CREATE PROCEDURE sp1_Insert
(
@iErrorCode int OUTPUT,
@number varchar(50),
@title varchar(300),
--@implementationdate datetime,
@implementationtype varchar(50),
@status bit,
@version1 decimal(9),
@version2 decimal(9),
@version3 decimal(9),
@version4 decimal(9),
@version5 decimal(9),
@version6 decimal(9),
@version7 decimal(9),
@version8 decimal(9),
@version9 decimal(9),
@version10 decimal(9),
@version11 decimal(9),
@version12 decimal(9),
@version13 decimal(9),
@version14 decimal(9),
@version15 decimal(9),
@version16 decimal(9),
@version17 decimal(9),
@version18 decimal(9),
@version19 decimal(9),
@version20 decimal(9),
@version21 decimal(9),
@version22 decimal(9),
@version23 decimal(9),
@version24 decimal(9),
@version25 decimal(9),
@version26 decimal(9),
@version27 decimal(9)
--@version28 decimal(9)
)
AS

-- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** --
INSERT into modification
( number,
title,
--implementationdate,
implementationtype,
status
)
VALUES
( @number,
@title,
--@implementationdate,
@implementationtype,
@status
)

-- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** --
Declare @iCount int, @modificationID int, @versionSection varchar(15)
Set @iCount = 1
Set @modificationID = @@IDENTITY

EXEC sp1_modification_section_addmodification @modification_id_1 = @modificationID

INSERT INTO modification_section_lnk(modification_id, section_id,version)
(SELECT @@Identity, 1,@version1 UNION
SELECT @@Identity, 2,@version2 UNION
SELECT @@Identity, 3,@version3 UNION
SELECT @@Identity, 4,@version4 UNION
SELECT @@Identity, 5,@version5 UNION
SELECT @@Identity, 6,@version6 UNION
SELECT @@Identity, 7,@version7 UNION
SELECT @@Identity, 8,@version8 UNION
SELECT @@Identity, 9,@version9 UNION
SELECT @@Identity, 10,@version10 UNION
SELECT @@Identity, 11,@version11 UNION
SELECT @@Identity, 12,@version12 UNION
SELECT @@Identity, 13,@version13 UNION
SELECT @@Identity, 14,@version14 UNION
SELECT @@Identity, 15,@version15 UNION
SELECT @@Identity, 16,@version16 UNION
SELECT @@Identity, 17,@version17 UNION
SELECT @@Identity, 18,@version18 UNION
SELECT @@Identity, 19,@version19 UNION
SELECT @@Identity, 20,@version20 UNION
SELECT @@Identity, 21,@version21 UNION
SELECT @@Identity, 22,@version22 UNION
SELECT @@Identity, 23,@version23 UNION
SELECT @@Identity, 24,@version24 UNION
SELECT @@Identity, 25,@version25 UNION
SELECT @@Identity, 26,@version26 UNION
SELECT @@Identity, 27,@version27)

Hope this helps...

Sunil