are there "arrays" in sql 2005 ?

Question

Post reply

are there "arrays" in sql 2005 ?

Viewing 15 posts - 16 through 30 (of 60 total)

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

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

dfalso (3/26/2008)
Right, that's what I wrote 🙂

Mid-day blindness due to lack of caffeine....hehe...

glad we agree:hehe:

----------------------------------------------------------------------------------
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?

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 2

Matt Miller (3/26/2008)
dfalso (3/26/2008)
Right, that's what I wrote 🙂
Mid-day blindness due to lack of caffeine....hehe...
glad we agree:hehe:

No prob, it'd be worse if we didn't :hehe:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

blahknow (3/26/2008)
Hi Gail,
You've got 3213 points, how did you get it, from another forum ?

Points are given for writing posts (1 per post writen) and for answering the Question of the Day.

I need to give people lists of phone numbers.
Any time i do that the number of people to whom i deliver the lists changes. People names change too.

Ok. Do the tables have to be named for the users? Do they have to be seperate tables? How are the lists used?

I think I can see a better way to do this, but I need a little more info first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shamshudheen SSCrazy Points: 2390 More actions · Answer 4

hi

there is no array types in sql server. but we can use function with temp table to work like arrays .

you can see the techniq from following address

http://www.sommarskog.se/arrays-in-sql.html

hope it will work

thanks

shamsudheen

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

Better you go for XML. Build one XML string at front end and pass it to the SProc. And inside that SProc use sp_preparexml, sp_removexml system SProcs to prepare and remove the XML respectively.

Refer below URLs as well.

http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx

Thanks,

MH-09-AM-8694

blahknow Right there with Babe Points: 773 More actions · Answer 6

shamshudheen (3/27/2008)
hi
there is no array types in sql server. but we can use function with temp table to work like arrays .
you can see the techniq from following address
http://www.sommarskog.se/arrays-in-sql.html
hope it will work
thanks
shamsudheen

Hi Shamshudheen

Thanks,

That what i did.

I'm curious what "xml" stands for, i never used it. I'll try the links given here, just to know what its all about.

Thanks.

blahknow Right there with Babe Points: 773 More actions · Answer 7

Mahesh Bote (3/28/2008)
Better you go for XML. Build one XML string at front end and pass it to the SProc. And inside that SProc use sp_preparexml, sp_removexml system SProcs to prepare and remove the XML respectively.
Refer below URLs as well.
http://www.sqlservercentral.com/articles/Stored+Procedures/2977/
http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx
Thanks,

Hi Mehesh,

I never used xml but i'll read your links at least for the sake of learning something i never done before.

Thanks

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Mahesh Bote (3/28/2008)
Better you go for XML.

A well formed UDF or open code that uses a Tally table to do the split will blow the doors off of any form of XML split. Side benefit is that the numbering that results from the Tally table method can actually be used to create an array-like coordinate system for the individual elements. Haven't tried it for 3 dimensions, but two dimensions (like a multicolumn table) is pretty easy to attain.

--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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

blahknow (3/25/2008)
Hi everyone
My stored procedure accepts 2 parameters: names, number of names like this: (name1, name2, name3,3) Problem is: the number of names changes each time i want to run it. Some time it is: Name1,1 and other time it is: name1, name2,2. So every time i run it i need to change code. I thought "arrays" might solve that problem but i never met arrays in sql2005. Does it exist ? Case answer is "no": What is it to replace arrays and solve the above problem?
Thanks

The Tally table (numbers table) method of splitting such inputs will be faster (and easier) than any other method including XML. The Tally table method is very similar to an "array" in that, if required, you can develop an (index) coordinate system for the elements much as you would have in VB or C for one and two dimensional "arrays" quite easily.

If you could post some real life examples of what you'd need to pass and how they differ from each other, I'd be happy to show you how to do this.

--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)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

We did tests in the past and found that for simple lists, UDF's were WAY better than XML. XML started to shine when we were working with more complex structures.

BTW, XML = eXtensible Markup Language, for those that asked.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

blahknow Right there with Babe Points: 773 More actions · Answer 11

Jeff Moden (3/28/2008)
blahknow (3/25/2008)
Hi everyone
My stored procedure accepts 2 parameters: names, number of names like this: (name1, name2, name3,3) Problem is: the number of names changes each time i want to run it. Some time it is: Name1,1 and other time it is: name1, name2,2. So every time i run it i need to change code. I thought "arrays" might solve that problem but i never met arrays in sql2005. Does it exist ? Case answer is "no": What is it to replace arrays and solve the above problem?
Thanks
The Tally table (numbers table) method of splitting such inputs will be faster (and easier) than any other method including XML. The Tally table method is very similar to an "array" in that, if required, you can develop an (index) coordinate system for the elements much as you would have in VB or C for one and two dimensional "arrays" quite easily.
If you could post some real life examples of what you'd need to pass and how they differ from each other, I'd be happy to show you how to do this.

Hi jeff,

This is how it works

"myPhones" is a stored procedure to supply the telemarketing personnel phone numbers to make phone calls.

phone numbers are stored in various tables of various names and same structure:

"phone_number" (varchar(50);

"date" (datetime).

in order to issue phone lists to telemarketing i activate that stored procedure with the following arguments:

-start date

-end date

-list of names

-name of table

the procedure uses the table which name is the argument: "name of table" and extracts all the phone numbers from that particular table (there are all kinds of tables:"clients", "members", "refusals", "no_contact_yet" etc..) with minimum date equals the parameter:"start date" and maximum date:"end date".

Once the phones list is ready, it should be devided to the number of personnel sent with the parameter "list of names". if one name is sent" that name will get the whole list, if the list (which i wanted as an array) is made of 10 names, that table should be devided into 10 "sub tables" each table bears the name of the personnel. For example, if "john", "marry" form the "list of names", the procedure will create 2 tables with the names: "john_table", "marry_table" and will send it by email to an email address i'd like to add to that array.

How would you create that array ? how would you devide it to the numbers of its' names ?

Thanks

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 12

blahknow (3/28/2008)
Hi jeff,
This is how it works
"myPhones" is a stored procedure to supply the telemarketing personnel phone numbers to make phone calls.
phone numbers are stored in various tables of various names and same structure:
"phone_number" (varchar(50);
"date" (datetime).
in order to issue phone lists to telemarketing i activate that stored procedure with the following arguments:
-start date
-end date
-list of names
-name of table
the procedure uses the table which name is the argument: "name of table" and extracts all the phone numbers from that particular table (there are all kinds of tables:"clients", "members", "refusals", "no_contact_yet" etc..) with minimum date equals the parameter:"start date" and maximum date:"end date".
Once the phones list is ready, it should be devided to the number of personnel sent with the parameter "list of names". if one name is sent" that name will get the whole list, if the list (which i wanted as an array) is made of 10 names, that table should be devided into 10 "sub tables" each table bears the name of the personnel. For example, if "john", "marry" form the "list of names", the procedure will create 2 tables with the names: "john_table", "marry_table" and will send it by email to an email address i'd like to add to that array.
How would you create that array ? how would you devide it to the numbers of its' names ?
Thanks

FWIW, (and I wrote about this a few posts ago), I wouldn't add that much complexity to your process since you don't have to. It's more simple to make a stored procedure that buckets the phones list by however many buckets you need (i.e. how many names supplied). So the stored procedure takes the parameter "number of buckets", and you don't have to deal with arrays in it. And it outputs one table with all the phone numbers bucketed, so you don't have to deal with a variable number of sub-tables.

Then, loop through your list of names, SELECTing bucket N, for the Nth name. Send that set to the user.

You also avoid dynamic SQL using this approach.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

dfalso (3/28/2008)
blahknow (3/28/2008)
Hi jeff,
This is how it works
"myPhones" is a stored procedure to supply the telemarketing personnel phone numbers to make phone calls.
phone numbers are stored in various tables of various names and same structure:
"phone_number" (varchar(50);
"date" (datetime).
in order to issue phone lists to telemarketing i activate that stored procedure with the following arguments:
-start date
-end date
-list of names
-name of table
the procedure uses the table which name is the argument: "name of table" and extracts all the phone numbers from that particular table (there are all kinds of tables:"clients", "members", "refusals", "no_contact_yet" etc..) with minimum date equals the parameter:"start date" and maximum date:"end date".
Once the phones list is ready, it should be devided to the number of personnel sent with the parameter "list of names". if one name is sent" that name will get the whole list, if the list (which i wanted as an array) is made of 10 names, that table should be devided into 10 "sub tables" each table bears the name of the personnel. For example, if "john", "marry" form the "list of names", the procedure will create 2 tables with the names: "john_table", "marry_table" and will send it by email to an email address i'd like to add to that array.
How would you create that array ? how would you devide it to the numbers of its' names ?
Thanks
FWIW, (and I wrote about this a few posts ago), I wouldn't add that much complexity to your process since you don't have to. It's more simple to make a stored procedure that buckets the phones list by however many buckets you need (i.e. how many names supplied). So the stored procedure takes the parameter "number of buckets", and you don't have to deal with arrays in it. And it outputs one table with all the phone numbers bucketed, so you don't have to deal with a variable number of sub-tables.
Then, loop through your list of names, SELECTing bucket N, for the Nth name. Send that set to the user.
You also avoid dynamic SQL using this approach.

Any code to go along with this "FWIW"? 😉

--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)

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 14

Jeff Moden (3/28/2008)
Any code to go along with this "FWIW"? 😉

What, you want answers too? 🙂

Well, assuming you have an integer-ish rowid field on the table, MOD it by the number of buckets you need. This is if the phone numbers are allowed to be randomly assigned, of course:

select *,

rowid_field % @BucketCount as bucket

from

where @BucketCount is a parameter to the proc.

Now, just loop through your array of people, with successive SELECT statements

where bucket = @ArrayIndex

Assuming your array is 0-based, you don't have to do anything. If it's 1-based (VB), either change your MOD calc:

(rowid_field % @BucketCount) + 1 as bucket

or decrement your array index in your where clause

where bucket = @ArrayIndex - 1

From what I read, I think this satisfies the problem. Right?

Unless I'm missing something...

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

dfalso (3/28/2008)
Jeff Moden (3/28/2008)
Any code to go along with this "FWIW"? 😉
What, you want answers too? 🙂
Well, assuming you have an integer-ish rowid field on the table, MOD it by the number of buckets you need. This is if the phone numbers are allowed to be randomly assigned, of course:
select *,
rowid_field % @BucketCount as bucket
from
where @BucketCount is a parameter to the proc.
Now, just loop through your array of people, with successive SELECT statements
where bucket = @ArrayIndex
Assuming your array is 0-based, you don't have to do anything. If it's 1-based (VB), either change your MOD calc:
(rowid_field % @BucketCount) + 1 as bucket
or decrement your array index in your where clause
where bucket = @ArrayIndex - 1
From what I read, I think this satisfies the problem. Right?
Unless I'm missing something...

Or....you could just use the NTILE function to achieve the same result...:)

----------------------------------------------------------------------------------
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?