#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0

    Question Using the IN Function with Stored procedure


    I have the following ASP Code:

    PHP Code:
    if(MLS ""then MLS 0
    if(MLS2 ""then MLS2 0
    if(MLS3 ""then MLS3 0
    if(MLS4 ""then MLS4 0
    if(MLS5 ""then MLS5 0
    if(MLS6 ""then MLS6 0


        
    '---------------------------
        ' 
    Database I/O
        dim oCon    
    set oCon server.CreateObject("ADODB.Connection")    : oCon.Open(Application("WebConnectionString"))
        
    dim SQL        SQL "EXEC proc_MLS @listingNumber='" MLS "','" MLS2 "','" MLS3 "','" MLS4 "','" MLS5 "','" MLS6 "'"
        
        
    set rsListings oCon.execute(SQL
    I have the following Stored Procedure:

    PHP Code:
    CREATE PROCEDURE proc_MLS

        
    @ListingNumber nvarchar(500)

        
    AS


    -- return 
    result
    select        
    *
    from        tblListings l
    left join        tblPersonnel p1    on 
    (l.ListingAgentID p1.PersonnelID)
    left join        tblPersonnel p2    on (l.SecondAgentID p2.PersonnelID)
    left join        tblCities    c    on (l.CityID c.CityID)
    left join        tblCounties co    on (c.CountyID co.CountyID)
    left join        tblStates s    on (co.StateID s.StateID)
    where        ListingNumber IN(@ListingNumber)
    and        
    Active 
    How do I make the multiple values get parsed correctly into the Stored procedure? I have tried the @ListingNumber= but i keep getting the Parameter Number 2 error and the @Name must come after the form. Any ideas from someone would be helpfull.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    a northern town
    Posts
    74
    Rep Power
    13
    I would created a var for each 'MLS' and call the SP with 6 or whatever parameters. So just declare each value in the SP individually and then build it up in the IN() clause.

    I can only assume that the command object thinks you are trying to pass multiple parameters when you are in fact only trying to pass one?

    HTH.
    /* measure twice, cut once */
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0

    Question Thanks


    I am actually trying to pass up to 6 mls #'s at once. However i tried building the var's up with the in() but got the parameter 5 and parameter 2 warning from sql that says I cannot pass the @var= after i have already called the first one in the form? I'm guessing maybe create an array or something? Thanks for the help!
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    a northern town
    Posts
    74
    Rep Power
    13
    What I meant was create your SP to expect 6 parameters and pass the six parameters to it during the call.

    PHP Code:
    CREATE PROCEDURE proc_MLS

        
    @ListingNumber1 nvarchar(12),
        @
    ListingNumber2 nvarchar(12),
        @
    ListingNumber3 nvarchar(12),
        @
    ListingNumber4 nvarchar(12),
        @
    ListingNumber5 nvarchar(12),
        @
    ListingNumber6 nvarchar(12),
        
    AS

    -- return 
    result 
    ...and call it with somethng like...
    PHP Code:
    SQL "EXEC proc_MLS('" MLS "','" MLS2 "','" MLS3 "','" MLS4 "','" MLS5 "','" MLS6 "')"
        
        
    set rsListings oCon.execute(SQL
    One thing to note that I just thought of is that you are assigning values to the var @listingnumber as you are passing it to the SP. I think this will not work. You need to assign the values to vars then pass them in order as my example.

    HTH?
    /* measure twice, cut once */
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0

    Thumbs up Thanks for the help


    Thanks for the help. That should work. Always good to get a new head thinking about it. Sometimes you get too bogged down on the problem that you miss the obvious.

IMN logo majestic logo spyfu logo threadwatch logo seochat tools logo