SQL Server - Stored Procedure values.... - Asked By Sree Rud on 19-Jul-14 02:48 AM

I am trying to pass a variable containing a string of values seperated by coma into a variable declared in my stored procedure. But this gives me error saying "too many arguments". Is there any other alternative to this in stored procedures ? When I pass a single value it works fine.
--------------------------
my code in stored procedure.........
---------------------------
CREATE PROCEDURE spProjectCustomerCustomerIDs
@PIDs varchar(500)
AS
CREATE TABLE ProjectCustomerID
(
ProjectName varchar(50) NOT NULL,
CustomerID varchar(10) NOT NULL
)
INSERT INTO ProjectCustomerID(ProjectName,CustomerID)
Select ProjectName,Customerid
From Projects
Where Projectid in(@PIDs)
------------------------------------------------
Thanks!
-----------------
ASP PAGE CODE
----------------------
The variable ProjectIDs has value something like this in my ASP Page..
ProjectIDs = 100,105,354,723,821,822,1213
Set RSProjectCust=oConn.Execute("exec prdiw..spProjectCustomerCustomerIDs " & ProjectIDs)
-------------------------------------
ERROR MESSAGE BELOW --
-----------------------------------
Microsoft OLE DB Provider for SQL Server error '80040e14'
Too many arguments were supplied for procedure spProjectCustomerCustomerIDs.
/mtb/BillableItems.asp, line 263
Reply  Edit   Title   Abuse
+10 +20 +30 +40 +50 ResetDelete Delete User
Try this...  -
Asked By Robbe Morris in SQL Server on 15-Jun-01 04:40 PM
...
ProjectIDs = '100,105,354,723,821,822,1213'
Check out this article on the home page:
http://www.eggheadcafe.com/articles/20001002.asp
It does what you are trying to do.

Try this... - Asked By Robbe Morris on 15-Jun-01 04:40 PM

...

 ProjectIDs = '100,105,354,723,821,822,1213' 

 Check out this article on the home page:
 http://www.eggheadcafe.com/articles/20001002.asp

 It does what you are trying to do.

This will work - Asked By Sandesh Moghe on 19-Jul-14 02:49 AM

instead of using Create Table statement directly,
use dynamic execution of stored procedue.
use sp_executesql systme procedure.
This will definitely solve your problem

Don't You know how to obtain returned data on ASP - Asked By Aleksei Jegorov on 27-Jun-01 07:11 AM

I tried to use command object, but EXECUTE method dont work properly...
Try This - SP replied to Sree Rud on 07-Jun-08 01:51 AM
Your Code ::

ProjectIDs = 100,105,354,723,821,822,1213

Set RSProjectCust=oConn.Execute("exec prdiw..spProjectCustomerCustomerIDs " & ProjectIDs)

Now Try This ::

Set RSProjectCust=oConn.Execute("exec prdiw..spProjectCustomerCustomerIDs '" & ProjectIDs & "'")