passing parameters to SQLserverstoredProcedure from VB(ADO)

[Solly]

Set prmExpenditure1 = cmdExpenditure.CreateParameter("allorsome", adVarChar, adParamInput, 1, gparam1)
cmdExpenditure.Parameters.Append prmExpenditure1
Set prmExpenditure2 = cmdExpenditure.CreateParameter("ProjectIDs", adVarChar, adParamInput, 200, gparam2)
cmdExpenditure.Parameters.Append prmExpenditure2

gparam1 = "s"
gparam2 = "'123','444'"

The SP is
CREATE PROCEDURE ProjectExpenditure_report
@allorsome varchar(1),
@ProjectIds varchar(200)
AS
If @allorsome = 's'
BEGIN
(SELECT ProjectID, Balance into temp1
FROM T1, T2
WHERE
t1.SubFundDetailId = t2.Trancd
and ProjectID IN (@ProjectIds)
GROUP BY ProjectID)
UNION ALL
(SELECT ProjectID,Balance FROM
TblBalance WHERE ProjectID IN (@ProjectIds))
ORDER BY 1,2,3
End
param1 works fine. Problem is with param2
If I give the values in the stored procedure directly for projectid then temp1 table is filled but the report comes up with no values.(Report doesn't have any conditions)

If I pass the values
rsProjExp.Open cmdExpenditure
then the temp table is not filled
and error 'unable to read records ' comes while executing
Report.readrecords in VB

I appreciate your help in advance.
[sig][/sig]

 

[sobeit]

My comments are based on the assumption that you're using MS SQL Server, so forgive me if my suggestions are wrong b/c it's not SQL Server.

With SQL, I have not been able to write a SP that evaluates the parameters as a string in the "IN" clause. It seems SQL Server always evaluate the parameter variable as one string, so an "IN" clause against a param never work like you want it to.

I always have to use the parameter passed in and build the SQL command in the SP. Here is an example a SP that I have to use:

CREATE PROCEDURE prcCustomer
(@FromDate datetime,
@DayRange int,
@HType varchar(100)
)
AS

select @SQL = "select distinct EffDate from tbtCustomer"
select @SQL = @SQL + " where EffDate >= '" + convert(varchar(12),@FromDate) + "' "
SELECT @SQL = @SQL + &quot; and EffDate <= '&quot; + convert(varchar(12),@ToDate) + &quot;'&quot;
select @SQL = @SQL + &quot; and Type IN(&quot; + @HType + &quot&quot;
select @SQL = @SQL + &quot; order by EffDate&quot;

Exec (@SQL)

I hope this help.


[sig][/sig]