ASP and SQL stored procedure - too many arguments

[lasers]

I have a SQL stored procedure like this:

CREATE PROCEDURE [dbo].[myStoredProc]
@NameList varchar(5000)
as
select * from dbo.MyTable
where UserName in (@NameList)
go

In my asp page I have this code:

nameList = "'john','mary','sue','dave'"

rs.open "exec myStoredProc " & nameList, dCon

I get this error:
Procedure or function myStoredProc has too many arguments specified.

What am I doing wrong? I'm assuming it has something to do with the way I pass the parameter to the stored proc.

 

[ggriffit]

lasers,
You are correct, SQL Server is seeing the single quotes around your nameList values, separated by comma's, as individual prameters rather than a single list.

You will need to either remove them - as per

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

or escape them so that it passes through correctly.

You should see the same issue if you run the SQL inside SQL Server.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/