Solved

Arrays in SQL?

Posted on 2008-06-26
5
286 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now