?
Solved

Arrays in SQL?

Posted on 2008-06-26
5
Medium Priority
?
291 Views
Last Modified: 2013-12-16
I am aware there are no arrays in SQL. Instead we use tables (read it online).

here is my situtation: I want to be able to make a link between a business we service and a category of colors for their logos.

for instance...
business 1 might have red and blue
business 2 might have Green and blue
business 3 might have red
business 4 might have Yellow and blue

I have the Category table

cat ID cat name
1         red
2         yellow
3         blue
4         green

I also have a cat_business Table...

CatID    BusinessID


I am adding the buisness through a asp.net 2.0 form and I have check boxes for the colors...

I know how to get the check boxes selected...
My question is how can I go about to add them to the database...

I want to add them when I add the business in my stored procedure...

Any help would be great.
Thanks.
0
Comment
Question by:copyPasteGhost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 5

Assisted Solution

by:buraksarica
buraksarica earned 800 total points
ID: 21873770
Everything is really ready for you now :)
All you need is adding the business and categories for that business in a transaction.
if you want to do this in a stored procedure, than you should pass the business details and category id's for the bussines. But category id's has no exact count? so you may pass the cat_id's comma delimeted string. And you can split these values with a function like below one. you can use this function like

Insert Into cat_business select *,@yourNewlyAddedBusinessID from Split(@yourStoredProceduresParameter , ',')
create function Split ( 
@StringToSplit varchar(2048),
@Separator varchar(128))
returns table as return
with indices as
( 
select 0 S, 1 E
union all
select E, charindex(@Separator, @StringToSplit, E) + len(@Separator) 
from indices
where E > S 
)
select substring(@StringToSplit,S, 
case when E > len(@Separator) then e-s-len(@Separator) else len(@StringToSplit) - s + 1 end) String
,S StartIndex        
from indices where S >0

Open in new window

0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21927157
I finally got a chance to try this out...

It's not working...

Msg 213, Level 16, State 1, Procedure usp_AddColorLink, Line 67
Insert Error: Column name or number of supplied values does not match table definition.

ALTER PROCEDURE [dbo].[usp_AddColorLink]
      -- Add the parameters for the stored procedure here
      @BID char(7),
      @Cats nvarchar(MAX)

AS
BEGIN
            
      INSERT INTO [dbo].[Category_Business]
            SELECT @BID,*
            FROM Split(@Cats , ',')
END

Any ideas?
Thanks.
0
 
LVL 22

Accepted Solution

by:
dportas earned 1200 total points
ID: 21928533
The error message means that your SELECT statement is returning more columns than exist in the Category_Business table. Best practice is to list all the columns in parentheses in the INSERT statement and list all the columns instead of using *. That way you can be sure the column lists match.

Example:

INSERT INTO [dbo].[Category_Business] (col1, col2, col)
SELECT col1, col2, col3, ...
0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21928753
right. I only have two coloumn in that table...

the catID and the Business ID.
0
 
LVL 13

Author Closing Comment

by:copyPasteGhost
ID: 31470926
>buraksarica Your functions looks really cool I didn't get it to work but thanks for the effort!
>dportas double thanks for your help!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question