Assigning 1-row & multi-column query result to local variable

Hello,
I am just new to T-SQL programming on SQL server 2000. I would like to
ask you if it is possible (like in VBA for example) to save the result
of query (1 row, but multi columns) to some "array" or "object"
variable in order to reference the concrete components (fields) of
this variable in the future code.
For example in VBA:
Dim query1 as Recordset
Set query1 = CurrentDb.OpenRecordset("ABC")
MsgBox query1!ID
MsgBox query1!Comment
Or is the only way to save the results to the temp table and use other
queries to select the required fields? This means more rows of code
and slowing of the whole calculation process.
Thank you very much for your answer.
MilanMilan,
What are you trying to accomplish?
AMB
"Milan" wrote:

> Hello,
> I am just new to T-SQL programming on SQL server 2000. I would like to
> ask you if it is possible (like in VBA for example) to save the result
> of query (1 row, but multi columns) to some "array" or "object"
> variable in order to reference the concrete components (fields) of
> this variable in the future code.
> For example in VBA:
> Dim query1 as Recordset
> Set query1 = CurrentDb.OpenRecordset("ABC")
> MsgBox query1!ID
> MsgBox query1!Comment
>
> Or is the only way to save the results to the temp table and use other
> queries to select the required fields? This means more rows of code
> and slowing of the whole calculation process.
> Thank you very much for your answer.
> Milan
>|||No arrays in SQL but you could do something like this:
select @.var1 = col1, @.var2 = col2 ... from tableName where IDcol = ...
The query must return just 1 row though for this to work. If I remember
correctly, if it returns more then 1 row, then the variables will receive
the values of the last row.
Maybe if you described what you are trying to do, then someone could find a
better solution.
"Milan" <milan_vaclavik@.centrum.cz> wrote in message
news:b4cdce36.0503090705.1f5d824e@.posting.google.com...
> Hello,
> I am just new to T-SQL programming on SQL server 2000. I would like to
> ask you if it is possible (like in VBA for example) to save the result
> of query (1 row, but multi columns) to some "array" or "object"
> variable in order to reference the concrete components (fields) of
> this variable in the future code.
> For example in VBA:
> Dim query1 as Recordset
> Set query1 = CurrentDb.OpenRecordset("ABC")
> MsgBox query1!ID
> MsgBox query1!Comment
>
> Or is the only way to save the results to the temp table and use other
> queries to select the required fields? This means more rows of code
> and slowing of the whole calculation process.
> Thank you very much for your answer.
> Milan|||SQL is a declarative language not a procedural one like VB. Storing
values from rows to variables is something you should generally try to
avoid. Instead of retrieving values and then referencing them in future
code, aim to write declarative, set-based code that operates on the
whole set of data at once. Your SQL code will be much cleaner, more
efficient and more maintainable that way. Don't try to use TSQL like it
was VB.
It is in fact possible to assign column values to variables, using a
SET or SELECT statement but variable assignment should be the exception
rather than the rule. Frequent use of variable assignment from tables
implies that you'll be using cursor based processing - a common error
made by programmers new to SQL. If you have an actual problem (the code
you posted already doesn't do anything useful that can't be achieved
with a SELECT statement) then please come back with more information so
that we can suggest an alternative.
David Portas
SQL Server MVP
--|||I would like to thank you for your replies.
I have the table with columns named like "A_01", "A_02", ..., "A_30",
"B_01", "B_02", ..., "B_30", "C_01" etc. I know it is badly designed but
I inherited it from my colleague. I have created a complex (and slow)
query which returns 1 row from this table. What I have to do now
(separately for A, B, C...) is to insert some calculated values (based
on concrete values of 01, 02,..., 30) to some other tables. For example
if A_01 = 6, I have to input A_02/6 to the A_03th, (A_03+1)th, ...,
(A_03+5)th column of some concrete table. The calculation is really very
complex.
In VBA this is a trivial task but i can not manage it easily in T-SQL.
Thank you for your ideas!
Milan
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hello Milan,
If it's easy for you to do in VBA, then why not do it in VBA? What requirem
ent
is there that you must do this in T-SQL?
Craig

> I would like to thank you for your replies.
> I have the table with columns named like "A_01", "A_02", ..., "A_30",
> "B_01", "B_02", ..., "B_30", "C_01" etc. I know it is badly designed
> but I inherited it from my colleague. I have created a complex (and
> slow) query which returns 1 row from this table. What I have to do now
> (separately for A, B, C...) is to insert some calculated values (based
> on concrete values of 01, 02,..., 30) to some other tables. For
> example if A_01 = 6, I have to input A_02/6 to the A_03th, (A_03+1)th,
> ..., (A_03+5)th column of some concrete table. The calculation is
> really very complex.
> In VBA this is a trivial task but i can not manage it easily in T-SQL.
> Thank you for your ideas!
> Milan
> *** Sent via Developersdex http://www.examnotes.net *** Don't just
> participate in USENET...get rewarded for it!
>|||I think you've realised that the root of your problem is the poor
design. I'm not sure why you would perpetuate this by creating another
table rather than do it in a view or query but anyway you may be able
to use something like this:
INSERT INTO Garbage_Out (a_01, a_o2, a_03)
SELECT I.a_01, NULL, I.a_02/I.a_01,
CASE I.a_01
WHEN 1 THEN I.a_0?
WHEN 2 THEN I.a_0?
..
END,
CASE I.a_01
WHEN 1 THEN I.a_0?
WHEN 2 THEN I.a_0?
..
END
FROM Garbage_In AS I
Fill in the question marks yourself - I wasn't clear from your
narrative which columns you would want to refer to. This "design" is
probably beyond redemption. Tables are not arrays.
David Portas
SQL Server MVP
--|||Hello Milan,

> In VBA this is a trivial task but i can not manage it easily in T-SQL.
Why not do it in VBA, then? What requirement is there that you do it in
T-SQL?
Craig