Inserting Comma-Delmited Lists into DB

[CrimsonDiva]

Hi!

I'm having a problem checking for the existence of a record in a table when the input is a comma-delimited list.

In the stored procedure below, I need to first check to see if a record exists in the database with the group and tool id's. If not, I want to add the new record. But something's wrong with my code. See below:

ALTER PROCEDURE sp_mysp
@groupid VARCHAR(1000),
@toolid INT
AS
IF NOT EXISTS(SELECT group_id, tool_id FROM group_tools WHERE group_id IN (@groupid) AND tool_id= @toolid)
-- insert new record into group_tools

The latter portion of the stored procedure works, but I know there's a problem some where around that IN operator.

Please help!
Thanks!

 

[evaleah]

Is the varchar(1000) your comma delimited list turned into a string? It seems it would be easier to do this in a DTS package where you could check that the group_id exists as a Source Column and if it does not then exexute your VBScript code to create the new record.

I might not be understanding your problem exactly. If not please let me know and I will try again.

 

[CrimsonDiva]

Thanks evaleah,

Yes, the varchar is the comma-delimited list that I'm passing to the stored procedure from my ASP page. I am unfamiiar with using DTS packages, which is why I'm using this method.

Some background: There are several checkboxes on a form that a user can select, all linked by IDs. I pass each of the selected IDs to the stored procedure and the procedure either adds the new id and tool to the table or not based on whether or not the record already exists.

 

[evaleah]

ok, that makes some sense then.

Can you send the IDs back not as a list but as individual ids? You could have your ASP page send the list back as an array, then loop through that array and check to see if the ids exists that way.

I had to do some research about how to loop through an array or parameters and unfortunately can't give you an example of that other than executing the Query more than once. Maybe someone else will be able to give you a good example of that.

Actually, since you are not really doing imports I take back my DTS recommendation. That isn't actually what you are looking for here.