Retrieving the value of the variables dynamically

Question

Retrieving the value of the variables dynamically

neltheboy

SSC Journeyman

Points: 92
More actions
November 7, 2007 at 6:08 am

#75809

I need your help!!!!!!!!!!!!!
I have variables @a1,@a2,@a3
I'll be assigning the values to these variables from a table having columns a1,a2,a3 using select @a1=a1,@a2=a2.... etc.
These columns contain same kind of data
i need to check the whether these variables are null or not and execute some sqls using IF BLOCK.
IF @a1 is not null
exec proc1 @a1
IF @a2 is not null
exec proc1 @a2
........
what i need to do is build these varibales @a1,@a2 inside a loop and use only one IF Block.
something like this
@cnt = 1
while @cnt<=3
loop
@var = '@a'+@cnt
IF @var is not null
exec proc1 @var
@cnt=@cnt+1
end loop
Thanks in advance

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

You've told us what you want to do... but not why you think you need to do it. This smacks of RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") on steriods and is probably not a good solution for whatever it is that you're trying to do. Perhaps more of an explantion about why you're trying to do this would help us understand so we can give you the best possible solution... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

neltheboy SSC Journeyman Points: 92 More actions · Answer 2

OK... from my client I am getting a table (structure predefined) of data which contains identical set (of 3 columns... in the previous example i mentioned only one column) of data ( repeats 24 times) along with other details in a single row for a particular ID. And i need to check each and every set( 24 times) whether that contains data or not and insert into a table, that means 24 inserts (inserts are performed using a procedure).

The table what I'm getting will have more than 2 million records.

it will look like this..

table1:

id1 name address a1 b1 c1 a2 b2 c2 .......a24 b24 c24

.....

......

if a1 is not null

insert into table2 values (a1, b1, c1)

if a2 is not null

insert into table2 values (a2, b2, c2)

.........

if a3 is not null

insert into table2 values (a24, b24, c24)

i think this will give you a much more clear picture.

Thanks

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

Why not

insert into table2 values (a1, b1, c1) where a1 is not null

insert into table2 values (a2, b2, c2) where a2 is not null

.........

insert into table2 values (a24, b24, c24) where a3 is not null

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

If you're starting out with a table, why are you extracting the data to variables, and then inserting the variable back into another table?

what about

Insert table2

select id1, name, address from table1 where id1 is not null

Isn't that easier? (and a WHOLE lot faster....)

Even if you're getting this "table" as a text file, there are lots of methods for treating that table as a "real" table (such as OPENQUERY, OPENXML, BCP, BULK INSERT, etc...) or inserting them in bulk into a temp table and then running from THAT.

That way, when this blows up from 24 rows to 240,000 rows, you won't have to go across town for coffee just to pass the time while your server chokes.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

neltheboy SSC Journeyman Points: 92 More actions · Answer 5

Thank you all for your comments.

Before inserting into the 2nd table i need to check the date fields present in each set, whether it is valid dates (some business logic is there) and we have to make one of these 24 sets as primary also while inserting into the 2nd table. that means before inserting into the 2nd table we need to have some sanity checks and decisions, which is not possible using a single query.

that is why i don't want to query the big table1 and insert into table2.

in one record itself i need to query 24 times, and for 2 million it will become

2*24 million. That is why i thought of using a loop and picking one record each into a set of variables and processing it.

That is why i'm planning to do this

I'll be using a loop and assigning the values to these variables from a table having columns a1,a2,a3 using select @a1=a1,@a2=a2.... etc.

These columns contain same kind of data

i need to check the whether these variables are null or not and execute some sqls using IF BLOCK.

IF @a1 is not null

exec proc1 @a1

IF @a2 is not null

exec proc1 @a2

........

what i need to do is build these varibales @a1,@a2 inside a loop and use only one IF Block.

something like this

@cnt = 1

while @cnt<=3

loop

@var = '@a'+@cnt

IF @var is not null

exec proc1 @var

@cnt=@cnt+1

end loop

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 6

No, no... I believe a checksum of each row would allow you to check for uniqueness across the 24 columns. Lookup CHECKSUM in BOL...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)