difficulty in passing parameter to SP in sql

happytony · July 10th, 2004, 01:54 AM

Hello,

I am trying to pass a set of ids to a stored procedure as a single parameter which is used for IN clause

the code is as folows:

Code:

"Select * From SAP_Achievements Where DealerID In (" & pstrDealerIDs & ") AND PCID = " & pintPCID & " AND ((Posting_Date) Between '" & (pdtFrom) & "'  AND '" & (pdtTo) & "')"

Here i am geting error that the parameter out of range.

I suppose the vaiable pstrDealerIDs is not considered as a single parameter when i call from the asp page

Code:

Set GetPCAchvmt = Server.CreateObject("ADODB.Recordset")
set strSQL = "exec GetPCAchvmt " & pstrDealerIDs &" , " & pintPCID &" , " &pdtFrom &" , " & pdtTo
set GetPCAchvmt = gconSFA.execute(strSQL)

please help me to pass the parameter

i am attaching the stored procedure with this request

Code:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  proc GetPCAchvmt
@pstrDealerIDs nvarchar,
@pintPCID smallint,
@pdtFrom datetime,
@pdtTo datetime
As
Select SalesAchievement = 
    Sum(Case 
    When Portal_Model_ID Is Not Null AND Doc_Type = 'RV' AND pstyu In ('ZMTN', 'TAN', 'ZMNN') AND DebitCredit_Indicator = 'S' Then Quantity
    When Portal_Model_ID Is Not Null AND Doc_Type = 'RV' AND DebitCredit_Indicator = 'S' AND quantity_indicator = 'S1' Then -1 * Quantity 
    When Portal_Model_ID Is Not Null AND Doc_Type = 'RV' AND pstyu = 'ZMRN' AND DebitCredit_Indicator = 'H' Then -1 * Quantity 
    Else 0 End ), 
    CollAchievement = 
    Sum(Case
    When Doc_Type = '5D' AND DebitCredit_Indicator = 'H' Then Amount
    When Doc_Type = '5A' AND DebitCredit_Indicator = 'H' Then Amount
    When Doc_Type = '5F' AND DebitCredit_Indicator = 'S' Then -1 * Amount
    Else 0 
    End)
    From SAP_Achievements 
    Where DealerID In (@pstrDealerIDs) 
    AND 
    PCID = @pintPCID
    AND 
    ((Posting_Date) Between @pdtFrom AND @pdtTo)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Information Systems
Videocon International Ltd
Aurangabad

happygv · July 11th, 2004, 05:00 AM

Hi there,

As per your SP code, you are passing 4 parameters to the SP, but as per the values you pass to the SP, it would consider as more than 4 parameters that causes this error. That is because, COMMA is used as a delimiter for the parameters list in the SP.

When you say

Code:

Execute MyProc a, b, c, d

would denote that you are passing 4 parameters, where the MyProc would be coded to take only one Paramter. That way you cannot pass commma separated values to be used inside the SP with IN operator.

You can go for a workaround in that case. Anything you would like to pass as parameter to be used with IN operator can be passed with someother delimiters, and will be replaced with comma inside the procedure as given below.

Code:

Create procedure MyProc @param varchar(25)
Declare @sql varchar(55)
set @sql = 'select * from YOURTABLE where ID in (' + replace(@param,'-',',') + ')'
exec(@sql)

Code:

Exec MyProc @param = '1-2-3-4-5-6'

Use anything other than COMMA there. - or : or _ or anything that doesn't have special meaning.

PS: REPLACE function works only with SQL server.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection