Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    1

    Unanswered: Regarding stored procedure parameters

    I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which having all 11 parameters.
    If I don’t enter any one of the value I am not getting the result and it will goes on exception handling.

    Now what I want is even I enter one value this must be excute………….i think some validations should be done in
    Stored procedure's 'where' clause.but I don’t know how

    i would be very happy if someone guides me with sample code for this.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I presume you mean your front end catches an exception. Correct me if wrong.

    You must either pass a value for all parameters (even a NULL will do) or put in default values for parameters you will not pass. This is no different to any other programming language if you think about it. Check out CREATE PROCEDURE in books online.

    Get your head round that then we can move on to the next bit.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EXEC ProcName "Value1", null, null, null, null, ect
    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
    May 2004
    Location
    Seattle
    Posts
    1,313
    even better, use a named parameter list so you don't confuse which is which:

    exec procname @param1='Value1', @p2=null, @p3=null, ...

    that way if some jackass adds a parameter into the middle of the list in the proc definition, your code will still work (that is unless it's a required param!)

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    even better, use a named parameter list so you don't confuse which is which:

    exec procname @param1='Value1', @p2=null, @p3=null, ...

    that way if some jackass adds a parameter into the middle of the list in the proc definition, your code will still work (that is unless it's a required param!)
    And you are not using ADO classic.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And I thought you couldn't do that with OUTPUT Parameters

    Until one guy did that and set the output variable

    I didn't think you could do that
    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.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also, don't pass the command as text. assuming you are using .net, use CommandType.StoredProcedure and add the params in the SqlCommand.Parameters collection.

    that way you are less vulnerable to sql injection attacks.

  8. #8
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    your where condition will be something like

    (@parameter1 = '' OR @parameter1 = null OR column = @parameter1 ) AND \ OR
    (@parameter2 = '' OR @parameter2 = null OR column = @parameter2 )

    so if any one of the condition is true your where clause will be executed
    and the value of your parameter, if no passed, should be null
    Last edited by nick.ncs; 07-02-07 at 03:48.

Posting Permissions

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