arrays in tsql?

Hi;

I have been writing a lot of short tsql scripts to fix a lot of tiny
database issues.

I was wondering if a could make an array of strings in tsql that I
could process in a loop, something like

array arrayListOfTablesToProcess = { "orders", "phone",
"complaints"}

for( int i = 0; i < arrayListOfTablesToProcess.length; i++ )
delete from arrayListOfTablesToProcess[i]

Can you do something like this in tsql?

I should probably stop asking all of these questions.

Is there a good book on TSQL alone ( I'm not interested in wizards,
just scripting )...that is short?

SteveThere are no arrays in TSQL. You are thinking in terms of a procedural
solution rather than a set-based solution. In general, you should be able to
use a table and set-based, SELECT/UPDATE/DELETE statements to process
"lists" of data. If you define your problem more specifically someone here
may be able to help with the set-based solution.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

In addition to David's post check out:

http://www.algonet.se/~sommar/arrays-in-sql.html

John

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0310040337.52427942@.posting.google.c om...
> Hi;
> I have been writing a lot of short tsql scripts to fix a lot of tiny
> database issues.
> I was wondering if a could make an array of strings in tsql that I
> could process in a loop, something like
>
> array arrayListOfTablesToProcess = { "orders", "phone",
> "complaints"}
> for( int i = 0; i < arrayListOfTablesToProcess.length; i++ )
> delete from arrayListOfTablesToProcess[i]
>
>
> Can you do something like this in tsql?
> I should probably stop asking all of these questions.
> Is there a good book on TSQL alone ( I'm not interested in wizards,
> just scripting )...that is short?
> Steve

Arrays in a Stored Procedure

Can any one help me with a sample code, which can take an array of

elements as one of it's parameters and get the value inserted into a table in a

stored procedure.

Thanks in advance

vnswathi.

SQL dosen't support array, so how do you want to passing an 'array' of elements as parameters to a stored procedure? Maybe you can use a string that delimits the elements of an array by some character(s) and then split the string into a set of values that can be inserted into a table. If so, the key point is how to split the string with specific delimitor, and you can take a look at this post:

http://forums.asp.net/thread/1300724.aspx

Arrays in a SQL2k DB

Hi,
I need to store a 2D Array in a Table.
What is the easiest way to to this. the array would only contain integer
values..
anyone a better idea than "implode" the Array in a string and "explode" it
again in the program code?
Nilshttp://www.aspfaq.com/2248
"Nils Wolf" <nisse@.gmx.at> wrote in message
news:e9bT6j4yFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to store a 2D Array in a Table.
> What is the easiest way to to this. the array would only contain integer
> values..
> anyone a better idea than "implode" the Array in a string and "explode" it
> again in the program code?
> Nils|||"Nils Wolf" <nisse@.gmx.at> wrote in message
news:e9bT6j4yFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to store a 2D Array in a Table.
> What is the easiest way to to this. the array would only contain integer
> values..
> anyone a better idea than "implode" the Array in a string and "explode" it
> again in the program code?
>
Well, storing it in a binary IMAGE column, or VARBINARY if it's small is a
whole lot better than using a string. Most programming environments know
how to convert integer arrays back and forth to byte arrays, and SQL Server
knows how to store and retrieve byte arrays.
But consider whether it's really best to hide the structure of your data
from SQL Server. Sometimes there's really no alternative, but if you can
find a way to model your data with tables and rows, it might be easier to
store, retrieve and manipulate, and if your arrays are sparse it may even be
more efficient.
David|||the Array is not so small.
it would be at least (100 x 100) up to (400 x 300)
the point is, that i need one array per row in a table with about 1000
rows...
Nils
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> schrieb im
Newsbeitrag news:uqw%23Wq4yFHA.2424@.TK2MSFTNGP12.phx.gbl...
> "Nils Wolf" <nisse@.gmx.at> wrote in message
> news:e9bT6j4yFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Well, storing it in a binary IMAGE column, or VARBINARY if it's small is a
> whole lot better than using a string. Most programming environments know
> how to convert integer arrays back and forth to byte arrays, and SQL
> Server knows how to store and retrieve byte arrays.
> But consider whether it's really best to hide the structure of your data
> from SQL Server. Sometimes there's really no alternative, but if you can
> find a way to model your data with tables and rows, it might be easier to
> store, retrieve and manipulate, and if your arrays are sparse it may even
> be more efficient.
> David
>|||Then IMAGE, not VARBINARY.
David
"Nils Wolf" <nisse@.gmx.at> wrote in message
news:uyqrCv4yFHA.3860@.TK2MSFTNGP09.phx.gbl...
> the Array is not so small.
> it would be at least (100 x 100) up to (400 x 300)
> the point is, that i need one array per row in a table with about 1000
> rows...
> Nils
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> schrieb im
> Newsbeitrag news:uqw%23Wq4yFHA.2424@.TK2MSFTNGP12.phx.gbl...
>

arrays

Thanks! I am aware that you can't pass an array to an sp. i was aware of tha
t
even when i asked that question. I simply did so, because I needed to prove
this to somebody.
"louise raisbeck" wrote:
> this post was a few days ago so no idea if you will all read this but just
> read this post as need to do a similar thing. this is an open forum to hel
p
> sql developers around the world and Joe your attitude was quite frankly ru
de
> and unnecessary and felt you needn't have bothered with the energy involve
d
> in replying.
> I am also writing a stored procedure and have to put an array of values in
to
> a stored procedure (from an asp.net drop down list). I may be wrong but I
> think that Preeta may have been asking how BEST to handle an array of valu
es,
> not how to declare an array in a stored procedure, which of course you can
t
> do. Preeta I have found that the best way to do this really is to pass a C
SV
> (comma delimited) list of values into a variable of nvarchar then split ou
t
> the values in the stored procedure.
> Or you could do what Joe suggested and go learn how to think and write
> proper sql code and dont ask a question until you do just that!!!!
> regards,
> "Joe Celko" wrote:
>you can't directly pass arrays, at least not in SQL Server.
However, there are some techniques that you can use, see:
http://www.sommarskog.se/arrays-in-sql.html

array?

Hello,
I was wondering if there is an alternative for an array in sql server.
What i would like (correct me if i approach this the wrong way) is an array
which i can use in a where clause based on a variable/parameter.
for example:
using a select statement to retrieve a list of titles or id's as integers.
titles:
book1
book2
book3
id's:
1
2
3
Pass that list into a variable/paramater of a second select statement like
select * from mytable where titles in @.variable/parameter. In this case the
values should be concated with quotes and commas like 'book1', 'book2'. If
the values are integer then 1, 2, 3.
Is this possible?
----
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.comhttp://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jason" <jlewis@.homail.com> wrote in message
news:ebwwjblHFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is an alternative for an array in sql server.
> What i would like (correct me if i approach this the wrong way) is an
> array which i can use in a where clause based on a variable/parameter.
> for example:
> using a select statement to retrieve a list of titles or id's as integers.
> titles:
> book1
> book2
> book3
> id's:
> 1
> 2
> 3
> Pass that list into a variable/paramater of a second select statement like
> select * from mytable where titles in @.variable/parameter. In this case
> the values should be concated with quotes and commas like 'book1',
> 'book2'. If the values are integer then 1, 2, 3.
> Is this possible?
> --
>
> ----
> This mailbox protected from junk email by MailFrontier Desktop
> from MailFrontier, Inc. http://info.mailfrontier.com
>|||Jason
CREATE PROCEDURE array_method_1
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC array_method_1
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
GO
"Jason" <jlewis@.homail.com> wrote in message
news:ebwwjblHFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is an alternative for an array in sql server.
> What i would like (correct me if i approach this the wrong way) is an
array
> which i can use in a where clause based on a variable/parameter.
> for example:
> using a select statement to retrieve a list of titles or id's as integers.
> titles:
> book1
> book2
> book3
> id's:
> 1
> 2
> 3
> Pass that list into a variable/paramater of a second select statement like
> select * from mytable where titles in @.variable/parameter. In this case
the
> values should be concated with quotes and commas like 'book1', 'book2'. If
> the values are integer then 1, 2, 3.
> Is this possible?
> --
>
> ----
> This mailbox protected from junk email by MailFrontier Desktop
> from MailFrontier, Inc. http://info.mailfrontier.com
>|||I have 6 different ways of doig this, at:
http://vyaskn.tripod.com/passing_ar..._procedures.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason" <jlewis@.homail.com> wrote in message
news:ebwwjblHFHA.1476@.TK2MSFTNGP09.phx.gbl...
Hello,
I was wondering if there is an alternative for an array in sql server.
What i would like (correct me if i approach this the wrong way) is an array
which i can use in a where clause based on a variable/parameter.
for example:
using a select statement to retrieve a list of titles or id's as integers.
titles:
book1
book2
book3
id's:
1
2
3
Pass that list into a variable/paramater of a second select statement like
select * from mytable where titles in @.variable/parameter. In this case the
values should be concated with quotes and commas like 'book1', 'book2'. If
the values are integer then 1, 2, 3.
Is this possible?
----
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

Array?

I have a query where I pull data based on a min function the gives me the
newest order. However when I pull the data I will get four records as I nee
d
to pull all the data for this order. What I need to know is how do I take
the data that I need assign a variable to it and only return one record.
Do I need to write a stored procedure that writes the data fields to a
variable and then bring them into my query?>I have a query where I pull data based on a min function the gives me the
> newest order. However when I pull the data I will get four records as I
> need
> to pull all the data for this order.
I'm lost. Please show DDL, sample data, and desired results.
http://www.aspfaq.com/5006|||Here is my code:
declare @.con as varchar(30)
set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus
where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus =
1 and
dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
--Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1 an
d
--dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_1612BinData.Station5c, dtbl_partList.partID,
dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
pckgPackageNumber, pkctSlotNumber
From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
dtbl_OrderStatus, dtbl_packagingDetails
Where @.con=dtbl_workOrder.corhOrderNumber and
dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
Here is my response:
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c partID
CustomerPartId OrderStatus
pckgPackageNumber pkctSlotNumber
----
---- --
-- -- -- --
----
---- --
---- --
2006-0100001 CRM
1,0 0,0 2,0
44040192,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 CURLKOA
1,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 222MID
115,0 47185920,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 22xMOD
5,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
I need to take the attvID and station information and string it out like
this...
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c attvId2 Station1.2 Station2.2
Station5a.2 Station5b.2 Station5c.2
Station1.3 Station2.3 Station5a.3 Station5b.3
Station5c.3
Station1.4 Station2.4 Station5a.4 Station5b.4
Station5c.4
partID CustomerPartId
OrderStatus pckgPackageNumber
pkctSlotNumber
Basically so it only returns one record. I will then need to pass this
information as a variable to another program.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm lost. Please show DDL, sample data, and desired results.
> http://www.aspfaq.com/5006
>
>|||I believe we asked for DDL, not your code. What you posted looks like
"camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
prefix?
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Your entire view of RDBMS is
wrong. You do not know that rows are not ANYTHING like a record, etc.
You have just been told by one of the people who wrote this language
that you do not know what you are doing. Think about that. Then tell
your boss, or I will. You need more help than you can get in a
Newsgroup.|||Hey Celko I really appreciate your help with this. Your the MAN!!
"--CELKO--" wrote:

> I believe we asked for DDL, not your code. What you posted looks like
> "camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
> prefix?
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Your entire view of RDBMS is
> wrong. You do not know that rows are not ANYTHING like a record, etc.
>
> You have just been told by one of the people who wrote this language
> that you do not know what you are doing. Think about that. Then tell
> your boss, or I will. You need more help than you can get in a
> Newsgroup.
>|||If I were in the position to need to do what you ask..
If there were always a max of 4 rows, and you can uniquely identify them,
I would create a user defined function which returns a table. THe table
definition would look like your code suggests.
You could insert the first row, then update it with the other rows of data -
appending to the trailing columns.
Then return the table with a single row...
Alternately you could create a stored procedure which has a local variable
for each value... populate the values with your 4 select statements, Then
SELECT the values out of the SP as a result set.
The first option is more flexible because it yeilds a table instead of a
result set...
Good luck, and hope this helps;
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"meverts" wrote:
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStat
us
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
=
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId OrderSta
tus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||>I believe we asked for DDL, not your code.
Correct, we did not get any CREATE TABLE or INSERT statements, so we have to
guess about a lot of things (or keep asking, maybe if we put sugar on top?).
However, the rest of your post is a useless tangent. Who cares if he uses
camelCase names? He still has a database problem he needs help with. Just
because his metadata, element names and table structure aren't identical to
what *you* would have created with the same task in front of you, doesn't
mean he should be ignored or told to go take a class or told that he is not
worthy of the newsgroup because he is not as smart as you.
I have been in the situation where the structure is NOT mine and I just have
to deal with it. I have also been in the situation where I've been made
responsible for parts of the project that did not align exactly with my core
competencies. And I have been in the situation where the guy that knew all
about it has been fired or left for other reasons. You should consider
giving people the benefit of the doubt instead of sh*tting on their head.
I have met you in person multiple times, I have shared lunch with you, and I
know that you can be a polite and even humble person. Why you choose to be
such a hateful, miserable know-it-all here, I have yet to figure out.
A|||No offense, but I can't really read this (particularly the results, which
may have looked good while composing your message, but certainly don't now).
Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
more concise version of your desired results, so we can understand exactly
what you need? You might need to see http://www.aspfaq.com/500 again.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder,
> dtbl_OrderStatus
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
> =
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
> and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId
> OrderStatus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Here are the ddl's for the three table from which my query uses.
Basically what I want to do is take 4 sets of data back.
I would like to figure out how to make this into one set.
CREATE TABLE [dtbl_workOrder] (
[corhOrderNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhVinNumber] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhSequenceNumber] [int] NOT NULL ,
[prdfId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wordDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordBatchQty] [int] NULL ,
[wordPreScheduleKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBatchIdSched] [int] NOT NULL ,
[wordBatchSerialSched] [int] NOT NULL ,
[wordReleaseOrder] [int] NOT NULL ,
[wordPackOrder] [int] NOT NULL ,
[wordProcessStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordProductionStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBackflushStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordPackStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordHold] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordCreationDateTime] [datetime] NULL ,
[wordUpdatedDateTime] [datetime] NULL ,
[wordReleaseDateTime] [datetime] NULL ,
[labelID] [numeric](18, 4) NULL ,
[wolvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asshId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[assuId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wcelId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSequence] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_workOrder] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_OrderStatus] (
[wordReleaseOrder] [int] NOT NULL ,
[OrderStatus] [int] NULL ,
[OrderFillDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSubmitDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_dtbl_OrderStatus] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_attributeList] (
[wordReleaseOrder] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[partId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attributeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[attnId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attribkey] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_attributeList] PRIMARY KEY CLUSTERED
(
[attribkey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Currently I return the following( I know this doesn't look very good)
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 CURLKOA 1,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 222MID 115,0 47185920,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 22xMOD 5,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
The desired results would be like this
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
CURLKOA 1,0 0,0 0,0 0,0 0,0 222MID 115,0
47185920,0 0,0 0,0 0,0 22xMOD 5,0 0,0 0,0
0,0 0,0 1655009B1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
I hope this helps. I appreciate you help. I obviously am very new at this,
and dont' know what I am doing, but sometimes in the real world you need to
learn on the job.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> No offense, but I can't really read this (particularly the results, which
> may have looked good while composing your message, but certainly don't now
).
> Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
> more concise version of your desired results, so we can understand exactly
> what you need? You might need to see http://www.aspfaq.com/500 again.
>
>
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
>
>|||In my experience, displaying multiple rows of data as one usually turns out
to be more trouble than it is worth. I reccomend looking for another way to
present the data, or possibly handling it in your app instead of in the
database. I've been forced to do what you are discussing in the past, and
have found ugly ways to do it, but I dont recomend it. The biggest problem
is that the number of rows which need to be consolidated has a tendency to
change over time, which means you are constantly updating your code to
match.
Revisit the reasons for this aproach and see if there is another way to
display this data that will fit your business need.
That said, if you absolutely have to do it this way, populating a temp table
in a stored procedure is better than any of the solutions I have used in the
past. It shouldnt be too much trouble to loop through the results and
insert/update the table as needed, then select from the table when you are
done.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:998EFAD9-7CCD-470D-9183-D89F8CB2FBFB@.microsoft.com...
> If I were in the position to need to do what you ask..
> If there were always a max of 4 rows, and you can uniquely identify them,
> I would create a user defined function which returns a table. THe table
> definition would look like your code suggests.
> You could insert the first row, then update it with the other rows of
data -
> appending to the trailing columns.
> Then return the table with a single row...
> Alternately you could create a stored procedure which has a local variable
> for each value... populate the values with your 4 select statements, Then
> SELECT the values out of the SP as a result set.
> The first option is more flexible because it yeilds a table instead of a
> result set...
> Good luck, and hope this helps;
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "meverts" wrote:
>
dtbl_OrderStatus
dtbl_OrderStatus.orderstatus =
and
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_partList,
OrderStatus
> -- -- -- --
-
Station2.2
the
as I

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

Array was not long enough. Check destIndex and length, and the array's lower bounds

When I try to export a report via Excel, I get an error of :

"Array was not long enough. Check destIndex and length, and the array's lower bounds."

Is this because the cell within Excel contains too many characters? There are two detail bands which could contain several pages worth of text.

I can export fine in HTML or PDF.

Thanks!

Hello,

We fix some problems with long text values in excel renderer and a hotfix is avaialble for RS2000 SP2.

Thank you,

Nico

|||

Nicoleta,

We already installed that SP2 since we were missing the printer icon when we first tried running the reports.

Is there a way to actually see which SP is on the server?

I tried looking at the Add / Remove Programs, and didn't see anything.

Thanks!

Array question

Is it possible to take a parameter that comes into a sproc that looks like
this:
'abc,xyx,ggg,ddd'
and put the elements which are separated by commas into an array?
Or is there any other method to take action on 'abc' and then 'xyx', and so
on.
Thanks,
don
sql 2000 or sql 7.0Hi,
There is no array concept in SQL Server. But there is an alternate way. See
the below URL:-
http://www.sommarskog.se/arrays-in-sql.html
Thanks
HAri
SQL Server MVP
"DonSQL2222" <DonSQL2222@.discussions.microsoft.com> wrote in message
news:259490C8-DABB-4C82-B963-E2B49C77C752@.microsoft.com...
> Is it possible to take a parameter that comes into a sproc that looks like
> this:
> 'abc,xyx,ggg,ddd'
> and put the elements which are separated by commas into an array?
> Or is there any other method to take action on 'abc' and then 'xyx', and
> so
> on.
> Thanks,
> don
> sql 2000 or sql 7.0
>|||> Is it possible to take a parameter that comes into a sproc that looks like
> this:
> 'abc,xyx,ggg,ddd'
> and put the elements which are separated by commas into an array?
SQL Server has absolutely no idea what you mean by array. But you can store
these values separately in a table, quite simply:
http://www.aspfaq.com/2248|||try
declare @.s varchar(1000), @.i int, @.val varchar(100)
select @.s = '123,456,789,000,'
select @.i = charindex(',', @.s, 1)
while @.i > 0
begin
select @.val = substring(@.s, 1, @.i - 1)
print @.val
select @.s = substring(@.s, @.i + 1, len(@.s))
select @.i = charindex(',', @.s, 1)
end
Rakesh
"DonSQL2222" wrote:

> Is it possible to take a parameter that comes into a sproc that looks like
> this:
> 'abc,xyx,ggg,ddd'
> and put the elements which are separated by commas into an array?
> Or is there any other method to take action on 'abc' and then 'xyx', and s
o
> on.
> Thanks,
> don
> sql 2000 or sql 7.0
>|||Thank you!! works perfectly. Just what i was looking for.
Don
"Rakesh" wrote:
> try
> declare @.s varchar(1000), @.i int, @.val varchar(100)
> select @.s = '123,456,789,000,'
> select @.i = charindex(',', @.s, 1)
> while @.i > 0
> begin
> select @.val = substring(@.s, 1, @.i - 1)
> print @.val
> select @.s = substring(@.s, @.i + 1, len(@.s))
> select @.i = charindex(',', @.s, 1)
> end
> Rakesh
> "DonSQL2222" wrote:
>

Array Problem

Hi All,

I have this code below:
Dim a As Integer = 0
While a <= myArray.Length()
Conn.Open()
Dim UpdateCmd As New SqlClient.SqlCommand("Update email_addr SET category = 'Deleted' where HP = @.hp", Conn)
UpdateCmd.Parameters.Add("@.hp", SqlDbType.VarChar, 50).Value = myArray(a)
UpdateCmd.ExecuteNonQuery()
a = a + 1
Conn.Close()
End While

BUt, When I run it, I receive error "Prepared statement '(@.hp varchar(50))Update email_addr SET category = 'Deleted' wher' expects parameter @.hp, which was not supplied. "

DOes anyone have the solution?

Thanks...

You are performing update in a LOOP ?

why not this :

update email_addr

set category = 'Deleted'

where HP in ('1', '2', '3', '4') -- array value here

Array params to stored procedures?

This is not obvious to me...

As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...

Ok, this meanswhen you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos)you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...

Is this correct?
Is there any other way to approach the problem?

Thanks a lot. -julioCorrect. There is no such things as array in TSQL|||You can easily get arround this by passing in XML|||Thanks pkr, i'll look at that...

Cheers to both. -julio

Array parameters in stored procedures in SQL 2005?

Hi,
I am not sure if I have understood what is happening with the new SQL server
2005.
Will we be using .Net classes instead of stored procedures?
Will I be able to use an array or list type of parameter for my queries with
SQL server 2005? If yes, how would I do this?
Thanks,
Morten> Will we be using .Net classes instead of stored procedures?
Well this is an option available with us. Not that this is the only way. The
T-SQL style still exists nevertheless.

> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
AFAIK, this is still not possible.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Morten" <morten@.imano.nospam> wrote in message
news:%23L9KEr6AFHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am not sure if I have understood what is happening with the new SQL
server
> 2005.
> Will we be using .Net classes instead of stored procedures?
> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
>
> Thanks,
> Morten
>|||>> Will I be able to use an array or list type of parameter for my
queries with SQL server 2005? <<
The short answer is no. But the real qustion is why do you want to
make SQL less relational instead of more standardized?
The way this is done in Standard SQL is with a table constructor:
BEGIN
DELETE FROM Parmlist;
INSERT INTO Parmlist
VALUES (a1), (a2), .., (an);
CALL Foobar (...);
END:
Then you use the parameter table in the query or other statement in the
usual manner.

Array Parameter

Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?

Yes, there is. In fact there is a sample at http://www.codeplex.com/MSFTEngProdSamples called "Array Parameter" which you can browse or download along with the other engine database samples. This sample contains code for passing an array to a CLR stored procedure using a CLR UDT. But be careful in SQL Server 2005 as you are limited in size to 8000 bytes. A future version of SQL Server is expected to relax that constraint. The other option which doesn't have that problem is to encode your data in XML and pass it using an XML parameter and rehydrate the objects in the CLR stored procedure.

Array of Value

I'm learning T-SQL, and I want to know what are the various ways to represent an array of values. For example, I might like to have an array of record IDs, which I feed to an "IN" clause. My guess is that such an "array" would be implemented by using a RecordSet. If yes, thats good - but what are the other ways (if any)? If no, then what other kinds of variables or parameters allow me to do something like that?You can use subquery for that like in:

WHERE SomeColumn IN (SELECT SomeColumn From SomeTable)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Search the forums for "split function". You can then pass values into a stored procedure with a varchar like "2,34,67,98" and have that string split into a set that can be used in an IN clause. These split functions basically break the string up and puts the CSVs into a temporary table and then (similar to the above suggestion) selects the values from the table.

|||But what about doing a select that has results stored in a (single-column) RecordSet? I've not tried it, but can't that RecordSet be passed around as a parameter, and then fed "as is" into an "IN" clause? Is that not a common/popular thing to do? If not, why not?|||

>>Is that not a common/popular thing to do?<<

Funny way to put this. Common? Yes. Popular? Yes. Natively supported in SQL Server? Nope.

The "classic" paper on the subject is here: http://www.sommarskog.se/arrays-in-sql.html

You can do it with a table variable, but sadly, you cannot pass this around as a parameter.

Array of CatalogItem[] Objects Returned from ListChildren

In the RS help documentation, the ListChildren method notes, "If no children
exist, this method returns an empty CatalogItem object."
So, I'm wondering how can I detect if no items were returned from my
ListChildren call.
I though something like this:
Reports.ReportingServices.CatalogItem[] items = null;
items = rs.ListChildren("/MasterReports", true);
if (items != null)
{
// not empty
}
else
{
// no children, empty catalog
}
Will this work? Again the documentation says it'll return an "empty"
catalog object, not necessarily a "null" object.
Thanks.Actually, just use:
if (items.length > 0)
process items...
else
no children...
end if
=-Chris
"dvorak" <dvorak@.nospam.com> wrote in message
news:%23zbBBIgGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> In the RS help documentation, the ListChildren method notes, "If no
> children
> exist, this method returns an empty CatalogItem object."
> So, I'm wondering how can I detect if no items were returned from my
> ListChildren call.
> I though something like this:
>
> Reports.ReportingServices.CatalogItem[] items = null;
> items = rs.ListChildren("/MasterReports", true);
> if (items != null)
> {
> // not empty
> }
> else
> {
> // no children, empty catalog
> }
>
> Will this work? Again the documentation says it'll return an "empty"
> catalog object, not necessarily a "null" object.
> Thanks.
>

Array manipulation

Hey all

I have a crystal report
I have a field in the database in table "1" that is a clob and stores codes separated by commas (e.g "1,343,4546,57657,67567,767,")

Then I have table "2" that has multiple records with the codes above in separate records
(e.g.
record 1 = 2
record 2 - 343
)

But not all codes are necessarily in table 2

So i want to separate the codes and show the ones that are not in table 2 as opposed to table 1

I created a formula field that has some manipulation
----------------
StringVar suppCodes:={table1.field(clob)};
stringvar array sort;
sort:=split(suppCodes, ",");

stringVar notresponded := "";
stringVar notr = "";

numberVar i ;

for i:=1 to ubound(sort) do(
if {table2.field} <> sort[i] then
notresponded := notresponded + " " + {table2.field}
)
------------------

But it is returning True and not the string notresponded

I want it to show me the string notresponded

Please someone helpSpot the difference:

stringVar notr = "";

stringVar notr := "";|||Didnt work cos I already dont use that var|||OK, then make it return notresponded, e.g.

for i:=1 to ubound(sort) do(
if {table2.field} <> sort[i] then
notresponded := notresponded + " " + {table2.field}
);

notresponded

Note the semicolon after the )
Should still fix the assignment though :)

Array List in WebService

Hi,

How i can get the FileNames that are stored in Web Server.

Dim strFiles As String = Server.MapPath("~/UploadedFiles/")

Dim dirinfo As New DirectoryInfo(strFiles)

dirinfo.GetFiles("*.doc")

The above Method Iam writing in Web Service. These files will display in a DataGrid in Windows Application.

Is this question for SSIS?

Thanks.

Array List in DB

Hi,
Is it possible to have a column as array in SQL Server 2000- similar to Oracle's varray. If yes, how?
rgds,
Kishoreyes, it is possible, but it is not recommended, because it will usually require table scans to access the contents|||I don't see a lot of point in Array fields when subtable give you the same functionality with less hassle and more control...|||subtable? what's a subtable? BOL is totally ignorant of that terminology|||I just haven't gotten around to writing that chapter yet. So busy posting on the forum, you know...|||What are either of you 2 talking about?

The only array stuff I've seen was in Ken Hendersons Book The Guru's Guide to SQL Server Stored Procedures, XML and HTML

Chapter 23...but that dealt with Dll's, ect...all very painful AND dangerous, if you weren't careful

Why, what do you need an array for?

PS A table is like an array anyway..you could use a table variable|||i believe blindman was actually just joking around

blindman, please let us know when you do this

as for my reply in post #2, yes, you can store an array as a comma-separated list of values in a single varchar column

i would've thought this would be obvious to any frequent poster here, and also why it's not a good idea|||as for my reply in post #2, yes, you can store an array as a comma-separated list of values in a single varchar column

That is not an array|||pedant!!

a lot depends on what programming language you have sitting on the front end

i know of several languages where you can simply assign the string to an array, and voila!

what would you call an array, please? and make sure you mention which language

;)|||You are both pedants!

When I get around to writing my Books Online chapter on subtables I will clearly define whether a comma-separated list is an array. Until then, you will just have to wait to find out.

And I am SERIOUS about this! ;)|||And I am SERIOUS about this! ;)Seriously what?

-PatP|||That is not an arrayTechnically, that's an unbounded vector. What's the difference though?

-PatP|||pedant!!

Hardly

a lot depends on what programming language you have sitting on the front end

Not really

i know of several languages where you can simply assign the string to an array, and voila!

Assign the string to an array..your words...the string is not an array

what would you call an array, please? and make sure you mention which language

An array is an object...pick you language...doesn't matter...they all behave the same way...|||brett, i know you can spell pedant (at least, so far), but do you actually know what it means?

"Assign the string to an array..your words...the string is not an array"

now that's pedantic!!|||God made google didn't he?

An array is more than data...Pat, I think you need to give up your "title" for Rudy...

How do you get the information rs.0 from a string for example?

Did you read Henderson's book?|||God made google didn't he?no, She didn't, and besides, that is such a non sequitur

An array is more than data...quite right, it is data plus relationship/sequencing

Pat, I think you need to give up your "title" for Rudy...if you are referring to "curmudgeon" then sorry, but you lead that race

Did you read Henderson's book?just once

a long time ago

:)|||Mathematically, an array is simply a bounded vector. Less formally, an array (http://searchstorage.techtarget.com/sDefinition/0,,sid5_gci211596,00.html) is a group of objects with the same attributes that can be addressed individually.

I ain't gonna give up my title without a fight! I'd be quite willing to add pedant (http://dictionary.reference.com/search?q=pedant) to my title though, if only they would let us (http://www.dbforums.com/t1070319.html)!

-PatP|||LOL, dude

which of the three subtitles did you wish to have?

1. One who pays undue attention to book learning and formal rules.
2. One who exhibits one's learning or scholarship ostentatiously.
3. Obsolete. A schoolmaster.

i'll cop to the last one only, i sure ain't the first

half the stuff i know i learned from you guys!

;)|||LOL, dude

which of the three subtitles did you wish to have?

1. One who pays undue attention to book learning and formal rules.
2. One who exhibits one's learning or scholarship ostentatiously.
3. Obsolete. A schoolmaster.

i'll cop to the last one only, i sure ain't the first

half the stuff i know i learned from you guys!

;)I'll just take pedant... Leave the determination of fit as an exercise for the reader ;)

Ich spreche sehr kliene Deutsche. You couldn't have learned half of what you know from me.

-PatP|||that's right

it's the other half

:cool:|||Is that like...

"I taught him everything I know...and he STILL doesn't know anything"

that is such a non sequitur

No it's not (although I am quite fond of them)...you were refering that I wouln't know what the word meant (which I didn't)...I mean A LOT of people who post here don't use Google...I use it all the time...soo...do I need to finish that thought?

if you are referring to "curmudgeon" then sorry, but you lead that race

Really? I'm not the one calling people pedant!!|||Really? I'm not the one calling people pedant!!really

you're being pedantic

that's far worse

;) :) :) :)|||You all sound like a pack of third-graders who have just been given dictionaries.

Seriously!|||I always sound like a third grader that just discovered a dictionary. I'm the only person I know who's cell phone has a collegiate dictionary on it!

Isn't pedant the word in the Pink Panther theme song?

I always thought that Orkin should license it as "dead-ant", but that would be too confusing with the aardvark on the same show... Ooops, I'm dating myself again, aren't I?

-PatP|||Those definitions for array sound very fishy. "a group of objects..."? That's a collection, which still uses the basic array concepts of storing and referencing data, but it's a collection. Don't you all remember your Intro to Computers 101? Isn't it just a linked list (I'll take double-linked list if that's what it is ;))?

Array in WHERE clause

Hello, how can I use an array in a WHERE clause?

If I had an array say

string[] NamesArray = new string[] {"Tom", "***", "Harry"}

How would I do this:

string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray"

Thanks in advance,

Louis

for a SQL Statement, you would need to serialize out the array of names into text (BTW that's "SELECT", not "SELET":

string myquery = "SELECT name, city, country FROM myTable WHERE name in ('george', 'jack', harry')

|||

Whatpbromberg wrote is correct, and here is another option which is also correct.

Insert you array items into a table (say MyArrayTable) and use this code:

1 SELET [name],2 city,3 country4FROM myTable5WHERE [name]in (6SELECT [Name]7 From MyArrayTable8 )9GO

Good luck.

|||

Thanks guys for your help.

It works,

Louis