Stored Proc Parameter Conversion Error

[Merkaba]

hi all!

i'm fixing bugs in an ASP web app that tracks employees skills for use in the field. The app calls a Stored Procedure, but when I checked the database, it didn't exist; it must have been lost somewhere. Sooo, I have to Create this Proc over. It is called like this:

SelectSql = "EXECUTE Skills_SEARCH @skillList = '" & _
            skills_array & "', @Project_ID = '" & _
            project_id & "'"

set get_employees = GetRS(SelectSql)

now skills_array = '1, 2, 3, 4...' or any array of skills id as a STRING. the Proc I made looks like this:

create procedure Skills_SEARCH
       @skillList as varchar(75)
       @project_id as int
as

SELECT u.id, u.first_name, u.last_name, s.user_id,
COUNT_BIG(s.skill_id) AS NoOfSkills from _users u 
LEFT JOIN _user_skills s ON s.user_id = u.id
LEFT JOIN PT_Project_Skills ps ON s.skill_ID = ps.skill_id
LEFT JOIN PT_Projects p ON ps.project_id = p.project_id
WHERE s.skill_id IN (@skillList) 
AND p.project_id = @project_id
GROUP BY u.id, u.first_name, u.last_name, s.user_id
ORDER BY NoOfSkills desc, u.last_name, u.first_name

Now, if I call the Proc from the ASP page I get the error:

Cannot convert type varchar to type int

Only when there is more than one element in the skill_array
The Proc works fine when one skill is picked, but as soon as the @skillList becomes a string array, it generates errors. I tried a bunch of different data types for @skillList, but none work right. sql_variant type won't generate errors, but it returns an empty recordset. If I manually type

...WHERE s.skill_id IN (1, 2, 3, 4)...

in Query Analyzer, it works fine. I'm thinking the only way to do this is to dynamically create the SQL statement based on the UBound of the String Array, but I have a feeling the stored proc has to work, cause someone before me had it working with a String Array going into a parameter on a stored proc. I was trying to use CAST in the statement but couldn't get that to work right either. Any Thoughts/Ideas????

Thanks.

Bob

 

[SQLSister]

Passing a list of values to a Stored Procedure faq183-3979
Passing a list of values to a Stored Procedure (Part II) faq183-5207


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Merkaba]

oops, forgot to check the FAQs first... I searched the forum for Array but got nothing, so i figured it didn't exist yet. Anyways, Thanks A Ton!

Bob