Solved

Arrays in SQL?

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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

696 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