Solved

Arrays in SQL?

Posted on 2008-06-26
5
289 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 200 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 300 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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