Concatinating Variables

[TinTin]

Hello,

How do I concatinate a variable. Here's the scenarios:

declare @var1 varchar(20)
declare @var2 varchar(20)
declare @var3 varchar(20)
declare @var4 varchar(20)
..
..
declare @var32 varchar(20)

set @var1 = 'Something 1'
set @var2 = 'Something 2'
....
set @var32 = 'Something 3'

/* I have to store the values of these individual variables. I wish to
have a "While" routine which iterates through the above variables. I
wish to have the variable name concatinated as that I do not have to
write numerous lines of code setting up individual 32 variables. How
could I use the '+' operator to join 'var' + @count . Where count is
from 1 through 32. I am having some trouble with the syntax.*/

Regards,
VS

[Erland Sommarskog]

Re: Concatinating Variables

[posted and mailed, please reply in news]

TinTin (lalalulu24@yah oo.com) writes:[color=blue]
> How do I concatinate a variable. Here's the scenarios:
>
> declare @var1 varchar(20)
> declare @var2 varchar(20)
> declare @var3 varchar(20)
> declare @var4 varchar(20)
> .
> .
> declare @var32 varchar(20)
>
> set @var1 = 'Something 1'
> set @var2 = 'Something 2'
> ...
> set @var32 = 'Something 3'[/color]

SELECT @concat = @var1 + @var2 + ... + @var32
[color=blue]
> /* I have to store the values of these individual variables. I wish to
> have a "While" routine which iterates through the above variables. I
> wish to have the variable name concatinated as that I do not have to
> write numerous lines of code setting up individual 32 variables. How
> could I use the '+' operator to join 'var' + @count . Where count is
> from 1 through 32. I am having some trouble with the syntax.*/[/color]

Nah, you don't have a syntax problem, you have a mindset problem. When
you work in T-SQL, looping is something you don't do that often. This
is a very different language from VB or C++.

While T-SQL does have some looping constructs, normally you operate on
sets of data at a time through tables. While this may be more difficult
to grok initially, this is necessity to get performance with any size
of data volume.

Since I don't know why you have these 32 variables, and what your actual
business problem is, I cannot really tell what you should do instead.
But you should probably not concatenate 32 variables.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: Concatinating Variables

Hi

You will probably run into scope problems with this approach.

There seems to be a design issue here, but without knowing more about the
table strucures and what you are actually trying to do it is hard to suggest
a better solution other than hard coding each one or (as per your previous
post) look at using a temporary tables.

John

"TinTin" <lalalulu24@yah oo.com> wrote in message
news:2d5425d1.0 406151243.3bf01 65a@posting.goo gle.com...[color=blue]
> Hello,
>
> How do I concatinate a variable. Here's the scenarios:
>
> declare @var1 varchar(20)
> declare @var2 varchar(20)
> declare @var3 varchar(20)
> declare @var4 varchar(20)
> .
> .
> declare @var32 varchar(20)
>
> set @var1 = 'Something 1'
> set @var2 = 'Something 2'
> ...
> set @var32 = 'Something 3'
>
> /* I have to store the values of these individual variables. I wish to
> have a "While" routine which iterates through the above variables. I
> wish to have the variable name concatinated as that I do not have to
> write numerous lines of code setting up individual 32 variables. How
> could I use the '+' operator to join 'var' + @count . Where count is
> from 1 through 32. I am having some trouble with the syntax.*/
>
> Regards,
> VS[/color]

[TinTin]

Re: Concatinating Variables

Hello John and Erland,

Thankyou for replying to my message.

As a matter or fact I have total of 52 variables.

These values are stored in an Excel spreadsheet. I use the DTS service
to import this spreadsheet into a temp table in SQL Server. Now I need
to catch these 53 different numeric values and parse them into
appropriate tabels in my Database. I use the Cursor to traverse
through the records in temp table and all these 53 values are Fetched
in 1 single record.

Hope this helps.

Regards!


"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<VbLzc.116 7$hb6.9819346@n ews-text.cableinet. net>...[color=blue]
> Hi
>
> You will probably run into scope problems with this approach.
>
> There seems to be a design issue here, but without knowing more about the
> table strucures and what you are actually trying to do it is hard to suggest
> a better solution other than hard coding each one or (as per your previous
> post) look at using a temporary tables.
>
> John
>
> "TinTin" <lalalulu24@yah oo.com> wrote in message
> news:2d5425d1.0 406151243.3bf01 65a@posting.goo gle.com...[color=green]
> > Hello,
> >
> > How do I concatinate a variable. Here's the scenarios:
> >
> > declare @var1 varchar(20)
> > declare @var2 varchar(20)
> > declare @var3 varchar(20)
> > declare @var4 varchar(20)
> > .
> > .
> > declare @var32 varchar(20)
> >
> > set @var1 = 'Something 1'
> > set @var2 = 'Something 2'
> > ...
> > set @var32 = 'Something 3'
> >
> > /* I have to store the values of these individual variables. I wish to
> > have a "While" routine which iterates through the above variables. I
> > wish to have the variable name concatinated as that I do not have to
> > write numerous lines of code setting up individual 32 variables. How
> > could I use the '+' operator to join 'var' + @count . Where count is
> > from 1 through 32. I am having some trouble with the syntax.*/
> >
> > Regards,
> > VS[/color][/color]

[TinTin]

Re: Concatinating Variables

Also:

53 values is the worse case scenario. Total number of variables could
be 3 to 53. Script needs to check how many total variables there are
and then likewise take action.
"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<VbLzc.116 7$hb6.9819346@n ews-text.cableinet. net>...[color=blue]
> Hi
>
> You will probably run into scope problems with this approach.
>
> There seems to be a design issue here, but without knowing more about the
> table strucures and what you are actually trying to do it is hard to suggest
> a better solution other than hard coding each one or (as per your previous
> post) look at using a temporary tables.
>
> John
>
> "TinTin" <lalalulu24@yah oo.com> wrote in message
> news:2d5425d1.0 406151243.3bf01 65a@posting.goo gle.com...[color=green]
> > Hello,
> >
> > How do I concatinate a variable. Here's the scenarios:
> >
> > declare @var1 varchar(20)
> > declare @var2 varchar(20)
> > declare @var3 varchar(20)
> > declare @var4 varchar(20)
> > .
> > .
> > declare @var32 varchar(20)
> >
> > set @var1 = 'Something 1'
> > set @var2 = 'Something 2'
> > ...
> > set @var32 = 'Something 3'
> >
> > /* I have to store the values of these individual variables. I wish to
> > have a "While" routine which iterates through the above variables. I
> > wish to have the variable name concatinated as that I do not have to
> > write numerous lines of code setting up individual 32 variables. How
> > could I use the '+' operator to join 'var' + @count . Where count is
> > from 1 through 32. I am having some trouble with the syntax.*/
> >
> > Regards,
> > VS[/color][/color]

[David Portas]

Re: Concatinating Variables

> through the records in temp table and all these 53 values are Fetched[color=blue]
> in 1 single record.[/color]

This sounds like a design problem too. Column values should be atomic values
not concatenated strings. On the other hand, 53 columns representing a list
of values seems unlikely to be the right design either: "Lists" are
analogous to *tables* not a set of values in a row.

Are you familiar with the concept of Normalization? Are you sure your
database is in at least Third Normal Form? If you don't have the correct
design to start with then you won't have the right foundation to build
concise, efficient and maintainable code and you'll have to struggle with
lots of cursors, loops and variables.

On the other hand, if you've already properly identified the entities and
attributes in your schema then post your CREATE TABLE statements so that we
can understand the problem and suggest how to tacke it.

Hope this helps.

--
David Portas
SQL Server MVP
--

[TinTin]

Re: Concatinating Variables

The table which I talk about is a temporary table which is created
within the procedure and would be deleted once I leave the stored
procedure. The sole purpose of the table would be to catch the values
from a seperate table which is extracted from Excel spreadsheet. It's
not dependent on any of the other tables in the database. Hence,
haven't looked upon Normalizing.

Here's the sample lines from the spreadsheet.I used the DTS to import
this into the database table.

Country Nigeria
Region African Continent
Gender Male Male Male Male Male Female
Female ...
Age Group 0-10 10-20 20-30 40-50 50-60 0-10
10-20 ...
Total 200 323 111 3232 333 555 333
..
..
..


Country Nicragua
Region African Continent
Gender Male Male Male Female Female Female
Age Group 0-4 15-20 20-30 0-4 15-20 20-30
Total 200 323 111 3232 112 441
..
..
..



Now.... Age group can have several more or a lot fewer age categories.
I need to capture the individual values in the 'Age Group' and
'Total'. That is why I need to have 53 variables (Worst Case). I use a
Cursor to iterate through each of the above lines. Once I capture the
information, I need to send it to my database structure.

What's the best way to design a Stored Procedure. Messier way is to
have 53 varbs. (worst case) and have 2 tempory tables to store the
above 2 rows (Age Group, Total). If I follow this approach, I need to
iterate through the fields in the temporary variables to make sure I
do not encounter any NULLs in between. For example, the 2nd example
above ( Nicragua) will have nulls in all the fields from field 6
through 53.

Following is the structure of the temp table
create table temptable1(
var1 varchar (10),
var2 varchar (10),
..
..
..
var53 varchar (10)
)

Regards,
VS




lalalulu24@yaho o.com (TinTin) wrote in message news:<2d5425d1. 0406160522.73ec b03@posting.goo gle.com>...[color=blue]
> Also:
>
> 53 values is the worse case scenario. Total number of variables could
> be 3 to 53. Script needs to check how many total variables there are
> and then likewise take action.
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<VbLzc.116 7$hb6.9819346@n ews-text.cableinet. net>...[color=green]
> > Hi
> >
> > You will probably run into scope problems with this approach.
> >
> > There seems to be a design issue here, but without knowing more about the
> > table strucures and what you are actually trying to do it is hard to suggest
> > a better solution other than hard coding each one or (as per your previous
> > post) look at using a temporary tables.
> >
> > John
> >
> > "TinTin" <lalalulu24@yah oo.com> wrote in message
> > news:2d5425d1.0 406151243.3bf01 65a@posting.goo gle.com...[color=darkred]
> > > Hello,
> > >
> > > How do I concatinate a variable. Here's the scenarios:
> > >
> > > declare @var1 varchar(20)
> > > declare @var2 varchar(20)
> > > declare @var3 varchar(20)
> > > declare @var4 varchar(20)
> > > .
> > > .
> > > declare @var32 varchar(20)
> > >
> > > set @var1 = 'Something 1'
> > > set @var2 = 'Something 2'
> > > ...
> > > set @var32 = 'Something 3'
> > >
> > > /* I have to store the values of these individual variables. I wish to
> > > have a "While" routine which iterates through the above variables. I
> > > wish to have the variable name concatinated as that I do not have to
> > > write numerous lines of code setting up individual 32 variables. How
> > > could I use the '+' operator to join 'var' + @count . Where count is
> > > from 1 through 32. I am having some trouble with the syntax.*/
> > >
> > > Regards,
> > > VS[/color][/color][/color]

[David Portas]

Re: Concatinating Variables

It seems that the purpose of your stored procedure is to transform the
spreadsheet data into a form that you can use, presumably in a table. It is
possible to do this kind of transformation in SQL, it just isn't pretty!
Here
goes.

First, assume you have your sample data loaded into your temp table. I've
added a row number to help us later. You would obviously do this bit in DTS:

CREATE TABLE TempTable1 (rowno INTEGER IDENTITY PRIMARY KEY, col1
VARCHAR(20) NULL, col2 VARCHAR(20) NULL, col3 VARCHAR(20) NULL, col4
VARCHAR(20) NULL, col5 VARCHAR(20) NULL, col6 VARCHAR(20) NULL, col7
VARCHAR(20) NULL, col8 VARCHAR(20) NULL)

INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nig eria')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','Afri can Continent')
INSERT INTO TempTable1 (col1,col2,col3 ,col4,col5,col6 ,col7,col8)
VALUES ('Gender','Male ','Male','Male' ,'Male','Male', 'Female','Femal e')
INSERT INTO TempTable1 (col1,col2,col3 ,col4,col5,col6 ,col7,col8)
VALUES ('Age Group','0-10','10-20','20-30','40-50','50-60','0-10','10-20')
INSERT INTO TempTable1 (col1,col2,col3 ,col4,col5,col6 ,col7,col8)
VALUES ('Total','200', '323','111','32 32','333','555' ,'333')
INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nic aragua')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','Afri can Continent')
INSERT INTO TempTable1 (col1,col2,col3 ,col4,col5,col6 ,col7)
VALUES ('Gender','Male ','Male','Male' ,'Female','Fema le','Female')
INSERT INTO TempTable1 (col1,col2,col3 ,col4,col5,col6 ,col7)
VALUES ('Age Group','0-4','15-20','20-30','0-4','15-20','20-30')
INSERT INTO TempTable1 (col1,col2,col3 ,col4,col5,col6 ,col7)
VALUES ('Total','200', '323','111','32 32','112','441' )

You haven't told us what the target structure is that you want to put the
data into. Based on your sample I'll assume it looks something like this:

CREATE TABLE SomeStats (country VARCHAR(20) NOT NULL /* REFERENCES Countries
(country) */, min_age INTEGER NOT NULL, max_age INTEGER NOT NULL, CHECK
(min_age>=0 AND max_age>min_age AND max_age<=120), gender CHAR(1) NOT NULL
CHECK (gender IN ('M','F')), stat INTEGER NOT NULL, PRIMARY KEY
(country,gender ,min_age))

In reality you would probably want to use codes rather than country names.
The Continent name obviously belongs in the Countries table rather than here
so I've left it out.

Create a view:

CREATE VIEW TransformStats
AS
SELECT rowno, 1 AS col, col1 AS stat
FROM TempTable1
UNION ALL
SELECT rowno, 2 AS col, col2
FROM TempTable1
UNION ALL
SELECT rowno, 3 AS col, col3
FROM TempTable1
UNION ALL
SELECT rowno, 4 AS col, col4
FROM TempTable1
UNION ALL
SELECT rowno, 5 AS col, col5
FROM TempTable1
UNION ALL
SELECT rowno, 6 AS col, col6
FROM TempTable1
UNION ALL
SELECT rowno, 7 AS col, col7
FROM TempTable1
UNION ALL
SELECT rowno, 8 AS col, col8
FROM TempTable1

Finally, insert your data:

INSERT INTO SomeStats (country, min_age, max_age, gender, stat)
SELECT country,
LEFT(age,CHARIN DEX('-',age)-1),
SUBSTRING(age,C HARINDEX('-',age)+1,20),
gender, stat
FROM
(SELECT
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Country'
AND rowno <= T.rowno)
AND col=2) AS country,
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Age Group'
AND rowno <= T.rowno)
AND col=T.col) AS age,
(SELECT LEFT(stat,1)
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Gender'
AND rowno <= T.rowno)
AND col=T.col) AS gender,
stat
FROM
TransformStats AS T
WHERE ISNUMERIC(stat) =1) AS T

This will of course fail if your spreadsheets aren't in a fairly regular,
predictable format. This is an unavoidable problem with spreadsheet data and
there isn't an easy solution except to find a reliable, structured data
source. Your data looks somewhat suspect anyway. Last time I checked my
atlas Nicragua [sic] wasn't in Africa :)

Hope this helps.

--
David Portas
SQL Server MVP
--