Storing the value from a select statement in a var

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

General SQL Server Forums

New to SQL Server Programming

Storing the value from a select statement in a var

Author

Topic

Shadab Shah
Starting Member

23 Posts

Posted - 2012-07-19 : 03:03:48

Hi all,

I have being trying to stored a value from a select statement in a variable . Since i know that arrays cannot be used in SQL Server, so what are the alternatives i can used to store the values. To Give a clear picture of what i am trying to do is,

col1
100
150
200
250

so i want variable which should be having each individual value. for example like @a=100, @b=150, @c=200, @d=250.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 03:28:14

It depends on what do you want to do with these values.

Best option is probably using a table variable.


declare @a table
(
    col1 int
)

insert into @a (col1)
select ... -- Your query here
from   ...

KH
[spoiler]Time is always against us[/spoiler]

Shadab Shah
Starting Member

23 Posts

Posted - 2012-07-19 : 05:46:51

I am a newbie and i tried to follow your answer and i landed on this:
declare @a table
(
col1 int
)
insert into @a(col1)(
select Sales from TableA)

which again i think is the same thing. I am creating a temporary table over here and the last value is being saved in the col1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 06:06:09

quote:
Originally posted by khtan

It depends on what do you want to do with these values.

What further processing or calculation that you need to do with these values ?

KH
[spoiler]Time is always against us[/spoiler]

Shadab Shah
Starting Member

23 Posts

Posted - 2012-07-19 : 06:15:34

Since i am a newbie i am just playing with data so i think now operations but still i may want to display them on screen. I know that i can display them using select * from tableA but i want individual variable to be displayed as select @a,@b,@c,@d.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 07:39:00

quote:
Originally posted by Shadab Shah

Since i am a newbie i am just playing with data so i think now operations but still i may want to display them on screen. I know that i can display them using select * from tableA but i want individual variable to be displayed as select @a,@b,@c,@d.

You might be able to do that if the number of records is small or fixed or sort of per-determined. Else how would you pre-declared the number of variables required.

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 07:53:46

When it comes to SQL programming, you have to change your mind-set from what you may be used to in procedural or object-oriented programming. Also, think of the database as a place to store and retrieve data while at the same time ensuring data integrity.

In your example, I would concur with KH that a table is the appropriate place to store that data. Then when you want to retrieve it, like you already said, you can retrieve the data using a SELECT.

If you want to associate a name with each value, then create a two-column table, one with the "variable name" and the other with the numbers. Then, you can get the value corresponding to any "variable" using something like:

SELECT valueColumn FROM YourTable WHERE nameColumn = 'c';

But, even that is not a scalable thing, as KH pointed out. Instead of the nameColumn, you probably would want a Sequential ID column that is perhaps autogenerated. Then you can query for the 374th "element" of the array, using a similar SELECT statement.

While database purists may glare at me for saying this, in a lot of ways, you can think of tables as being the array equivalent in SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources