How do I make the name of a variable dynamic?

Hi guys, I have the following store procedure:

PROCEDURE dbo.AddSearchColumn (@.A1, @.A2. @.A3, @.A4, @.A5) AS
Declare @.cElements cursor,@.DocNum varchar(100)
BEGIN
Set @.cElements = cursor for select FirstNaname rom dbo.DocTable1
open @.cElements
fetch NEXT from @.cElements into @.DocNum

while (@.@.FETCH_STATUS = 0)
begin
if (@.DocNum==@.A1 //Here is what I need to do: I want use @.A1 at the first loop step, @.A2 at the second, and so on
...... // @.A1, @.A2 are the parameters in input
end
END

close @.cElements
DEALLOCATE @.cElements

I hope my explanation is clear.

Please, give some hints.
Thanks for your time,

Christian Malatesti

Can you explain in more detail what it is that you are trying to do? From what I understand you want to compare the values in the parameters against "FirstNaname" from table docTable1?

|||

What I am trying to do is this:

I have several parameters in input: @.A1, @.A2, @.A3, @.A4,...and so on (they are more than four) that I need to use

while looping through the elements of the cursor.

I know the order that the input parameters need to be used.

At the first step of the loop I have to compare the first element retrieved by the cursor with @.A1.

At the second step of the loop I have to compare the second element retrieved by the cursor with @.A2.

At the third step of the loop I have to compare the third element retrieved by the cursor with @.A3.

and so on...

What I would like to accomplish is creating a variable (@.dynamicVariable) that at the first contains the value of @.A1, atthe second step, @.dynamicVariable contains the value of @.A2, andso on...

Below is a basic example.

Declare@.dynamicVariable , @.counter int

set @.counter=1;

while (@.@.FETCH_STATUS = 0)
begin

set @.dynamicVariable = "@.A" + ToString(@.counter)
if (@.DocNum==@.dynamicVariable....

//something like this:

set @.counter=@.counter + 1;
end
END

Thanks for responding me.

Christian Malatesti

|||Is the number of parameters fixed?|||YES, i have 17 parameters in input|||And you will always have 17 values returned from your SQL statement?|||

I do not have any parameters in output.

The input parameters are used to perfrom various INSERT, that I did not include in the post.

Christian

|||

Perhaps you can get the results into 2 tables (table variables) each with identity columns and compare by rowid?