sql server programming:
[quoted text, click to view]
How about just supressing duplicates returned from dbo.fn_intlist_to_table?


INSERT INTO LinkTable (ID1, ID2)
SELECT DISTINCT @intNewID, L.number
FROM dbo.fn_intlist_to_table(@chvLinks, DEFAULT) L
WHERE L.number > 0 AND L.Number <> @intNewID

David

[quoted text, click to view]

How is this links list built? Couldn't you prevent a single value from
appearing twice?

Or, instead of treating each one separately, use an IN clause... then it
doesn't matter how many times a single value is listed.

[quoted text, click to view]

You can use JavaScript to fiddle with the list, or prevent multiple
selection, before the list ever got to the server. You could also filter
the list down before you send it to the database.

[quoted text, click to view]

Well, assuming these id numbers come from another table, you could say

INSERT newtable SELECT @id, number
FROM othertable WHERE number IN (1,2,3,5,3)
GROUP BY @id, number

Hi all.

SQL 2k Dev ed
Win 2k Pro


I have a "Link table" to store link IDs between two other tables

LinkTable
ID1 INT
ID2 INT

Primary Key = ID1, ID2

I call a stored Proc (from ASP)

MyProc (title, description, links)

it adds a new row with the title & description and gets the new ID
back for row.

select @intNewID = scope_identity()

then I add links into the link table, the links are passed into the
stored proc as a string with numbers seperated by commas

eg links = "1,2,3,4,5,3,6,7'

the function dbo.fn_intlist_to_table() returns the links list as a
table with 8 rows 1 column.

What I want to add into the Link table is the following values.
NewID, 1
NewID, 2
NewID, 3
NewID, 4
NewID, 5
NewID, 3 <<<< Except for this one!!!
NewID, 6
NewID, 7

but as you can see the number "3" is in the links list TWICE... which
means when it adds it to the LinkTable it fails with the following
error:

Violation of PRIMARY KEY constraint 'PK_LinkTable '. Cannot
insert duplicate key in object 'LinkTable'.

This is the Code I'm using to put the values into the LinkTable

INSERT INTO LinkTable (ID1, ID2)
SELECT @intNewID, L.number
FROM dbo.fn_intlist_to_table(@chvLinks, DEFAULT) L
WHERE L.number > 0 AND L.Number <> @intNewID


How can I do the same thing but if the row already exists in the
LinkTable just ignore the current link and move onto the next one.

Thanks for any help on this.

Alan

Hi

The links list is taken from several drop down boxes on the HTML front
end that lists the options. but the user could select the same link in
2 seperate boxes (which he shouldnt do)

IN clause? how would I use that in this situation?

Thanks for the help.

On Fri, 2 Jan 2004 16:49:42 -0500, "Foo Man Chew" <foo@man.chew>
[quoted text, click to view]