Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: Parameters passed to Stored Procedure from ASP

    I'm relatively new to stored procedure writing. My situation is an ASP page allowing multiple selections from a <select> option passing 1 to x number of options to a stored proc. for the WHERE clause.
    For instance, a user selects 1,3,5 and 6. These would need to be passed to the sp and then:

    ...FROM [tablename]
    WHERE (Number = @param1) OR (Number = @param2) OR (Number = @param3) OR (Number = @param4) etc....

    This sp ties into a Crystal Report and in the above scenario, should return data for colums equal to 1 3 5 and 6. If only 1 and 6 had been selected on the ASP page, then only those two would be assigned a value in the sp.

    Any suggestions?
    Thanks in advance...

    IIS 5.0, Win 2k, MSSQL 7.0

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Just create an ado connection/command objects and execute the stored procedure. What are the ranges for the parameters ?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Looks like you want to say something like


    WHERE col1 IN (@param1, @param2, @param3, @param4)

    Or

    WHERE col1 IN (@param1, @param2)

    whatever the case may be...is that right?

    Or are they diferent columns
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2003
    Posts
    4
    Well, if the ASP was passing three different parameters (Start Date, End Date, Details) they would be passed to the sp like(whereas the strStartDate, strEndDate and strDetails were assigned the Request.Form values):

    Set ThisParam = StoredProcParamCollection.item(1)
    ThisParam.SetCurrentValue cstr(strStartDate), 12

    Set ThisParam = StoredProcParamCollection.item(2)
    ThisParam.SetCurrentValue cstr(strEndDate), 12

    Set ThisParam = StoredProcParamCollection.item(3)
    ThisParam.SetCurrentValue cstr(strDetails), 12

    However, in my scenario, I need to allow for multiple selections in one Request.Form("select") collection so to speak passed to the sp.

    Does that make sense??
    :-\

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    What is the maximum number of selections in the select box - and will this keep growing ?

  6. #6
    Join Date
    Nov 2003
    Posts
    4
    The select box as 12 selections. The user can pick as few as one or as many as all. Basically, any combination. Say they pick 1 & 2. I need to pass those selections to the sp and use those parameters in the where

    WHERE [columnname] = @parameter1 or [columnname] = @parameter2 or [columnname] = @parameter3(parameter 3 remains default value since only 1 and two were passed in)

    (The parameters are assign the passed values or remain default if no value passed)
    Last edited by war_manelli; 11-12-03 at 16:11.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How come you don't ask for a variable result set from the SELECT..can't they pick their own fields too?

    Don't want to use the D word.....

    Have you run a sql statement with all 13 parameters?

    Can we see the sproc...

    Is it like CREATE PROC mySproc @Param1 = null, @Param2 = null

    Maybe you can pass all of them

    and do WHERE Col1 = ISNULL(@Param1,Col1) AND...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    You could set up one parameter and pass a delimited string containing all your selections.

    You then split the string up and use the in statement to do your selection.

    It's not pretty but it will work.

    Let me know if you want details of how to do this.

  9. #9
    Join Date
    Nov 2003
    Posts
    4
    Brett & rokslide, thanks for the help. Basically, the options aren't added from a db connection into the asp page. So, hard coding the 12 values (which never change as they are campus locations) isn't a problem. So, the intent was to pass any combination of selected campuses, pass them to a sp and generate a report for the campuses selected.

    ...FROM [tablename]
    WHERE (Number = @param1) OR (Number = @param2) OR (Number = @param3) OR (Number = @param4) etc....

    Is basically what I need to do, but I like the delimited idea and splitting the string and doing the select through iteration.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •