ASP & Stored Procedures!

Discussion in 'ASP General' started by Anthony Judd, Oct 22, 2004.

  1. Anthony Judd

    Anthony Judd Guest

    Hi all,

    Just a quick question. I am using Sql Server 2000 and calling stored
    procedures from asp.
    At this stage i am forgoing using the ADO command object and am simply
    create dynamic like sql statement for store procedure execution.

    My question is, I find the code below quite messy, particularly when it
    comes to checking for option parameters using if statements.
    Is their a better way to write this, keeping in mind i have to check in a
    value exists before adding the appropriate parameter
    to the stored procedure call.

    Is there are more elegant way?

    Thanx
    AJ

    'call stored procedure to store event schedule item
    sql = "Exec add_schedule_item " &_
    "@event_id =" & Request.Form("event_id") &_
    ",@venue_id =" & Request.Form("venue_id")
    If(Request.Form("dress_category_id")<>"") Then sql = sql &
    ",@dress_category_id =" & Request.Form("dress_category_id")
    sql = sql & ",@fee_id=" & Request.Form("fee_id")
    If(Request.Form("sponsor_id") <> "") Then sql = sql & ",@sponsor_id =" &
    Request.Form("sponsor_id")
    If(Request.Form("schedule_duration") <> "") Then sql = sql &
    ",@schedule_duration=" & Request.Form("schedule_duration")
    sql = sql & ",@schedule_start_date = '" &
    Request.Form("schedule_start_date") & "'"
    If(Request.Form("schedule_rsvp_date") <> "") Then sql = sql &
    ",@schedule_rsvp_date = '" & Request.Form("schedule_rsvp_date") & "'"
    sql = sql & ",@schedule_start_time = '" &
    Request.Form("schedule_start_time") & "',"
    sql = sql & "@schedule_end_time = '" & Request.Form("schedule_end_time")
    & "'"
    Anthony Judd, Oct 22, 2004
    #1
    1. Advertising

  2. One way to make it a bit nicer would be to break things into subs and
    functions, i.e.

    Also, if the value of a form is empty, does that matter? Your stored
    procedure shouldn't care, and if you need it to care, you can set it to have
    default values for values not passed.

    CREATE PROC add_schedule_item (@event_id int = 0....


    Dim sSQL
    Dim EventID, VenueID, DressID, FeeID, SponsorID, SchedDuration, StartDate,
    RSVPDate, StartTime, EndTime

    Call GetValues()
    sSQL = "EXEC add_schedule_item " & _
    "@event_id=" & EventID & "," & _
    "@venue_id=" & VenueID & "," & _
    "@dress_category_id=" & DressID & "," & _
    '...etc.
    "@schedule_start_time=" & sIn(StartTime) & "," & _



    Function sIn(s)
    sIn = "'" & Replace(s, "'", "''") & "'"
    End Function

    Sub GetValues()
    EventID = Request.Form("event_id")
    VenueID = Request.Form("venue_id")
    '....
    End Sub


    Ray at work


    "Anthony Judd" <> wrote in message
    news:...
    > Hi all,
    >
    > Just a quick question. I am using Sql Server 2000 and calling stored
    > procedures from asp.
    > At this stage i am forgoing using the ADO command object and am simply
    > create dynamic like sql statement for store procedure execution.
    >
    > My question is, I find the code below quite messy, particularly when it
    > comes to checking for option parameters using if statements.
    > Is their a better way to write this, keeping in mind i have to check in a
    > value exists before adding the appropriate parameter
    > to the stored procedure call.
    >
    > Is there are more elegant way?
    >
    > Thanx
    > AJ
    >
    > 'call stored procedure to store event schedule item
    > sql = "Exec add_schedule_item " &_
    > "@event_id =" & Request.Form("event_id") &_
    > ",@venue_id =" & Request.Form("venue_id")
    > If(Request.Form("dress_category_id")<>"") Then sql = sql &
    > ",@dress_category_id =" & Request.Form("dress_category_id")
    > sql = sql & ",@fee_id=" & Request.Form("fee_id")
    > If(Request.Form("sponsor_id") <> "") Then sql = sql & ",@sponsor_id =" &
    > Request.Form("sponsor_id")
    > If(Request.Form("schedule_duration") <> "") Then sql = sql &
    > ",@schedule_duration=" & Request.Form("schedule_duration")
    > sql = sql & ",@schedule_start_date = '" &
    > Request.Form("schedule_start_date") & "'"
    > If(Request.Form("schedule_rsvp_date") <> "") Then sql = sql &
    > ",@schedule_rsvp_date = '" & Request.Form("schedule_rsvp_date") & "'"
    > sql = sql & ",@schedule_start_time = '" &
    > Request.Form("schedule_start_time") & "',"
    > sql = sql & "@schedule_end_time = '" & Request.Form("schedule_end_time")
    > & "'"
    >
    >
    >
    Ray Costanzo [MVP], Oct 22, 2004
    #2
    1. Advertising

  3. Anthony Judd

    Mike D Guest

    This link was posted for me yesterday by Bob Barrows maybe it will help you.
    http://tinyurl.com/jyy0


    "Anthony Judd" wrote:

    > Hi all,
    >
    > Just a quick question. I am using Sql Server 2000 and calling stored
    > procedures from asp.
    > At this stage i am forgoing using the ADO command object and am simply
    > create dynamic like sql statement for store procedure execution.
    >
    > My question is, I find the code below quite messy, particularly when it
    > comes to checking for option parameters using if statements.
    > Is their a better way to write this, keeping in mind i have to check in a
    > value exists before adding the appropriate parameter
    > to the stored procedure call.
    >
    > Is there are more elegant way?
    >
    > Thanx
    > AJ
    >
    > 'call stored procedure to store event schedule item
    > sql = "Exec add_schedule_item " &_
    > "@event_id =" & Request.Form("event_id") &_
    > ",@venue_id =" & Request.Form("venue_id")
    > If(Request.Form("dress_category_id")<>"") Then sql = sql &
    > ",@dress_category_id =" & Request.Form("dress_category_id")
    > sql = sql & ",@fee_id=" & Request.Form("fee_id")
    > If(Request.Form("sponsor_id") <> "") Then sql = sql & ",@sponsor_id =" &
    > Request.Form("sponsor_id")
    > If(Request.Form("schedule_duration") <> "") Then sql = sql &
    > ",@schedule_duration=" & Request.Form("schedule_duration")
    > sql = sql & ",@schedule_start_date = '" &
    > Request.Form("schedule_start_date") & "'"
    > If(Request.Form("schedule_rsvp_date") <> "") Then sql = sql &
    > ",@schedule_rsvp_date = '" & Request.Form("schedule_rsvp_date") & "'"
    > sql = sql & ",@schedule_start_time = '" &
    > Request.Form("schedule_start_time") & "',"
    > sql = sql & "@schedule_end_time = '" & Request.Form("schedule_end_time")
    > & "'"
    >
    >
    >
    >
    Mike D, Oct 22, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Elliot M. Rodriguez, MCSD

    Re: crystal reports and asp.net stored procedures

    Elliot M. Rodriguez, MCSD, May 21, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    472
    Elliot M. Rodriguez, MCSD
    May 21, 2004
  2. .Net Sports
    Replies:
    3
    Views:
    603
    Robbe Morris [C# MVP]
    May 12, 2005
  3. Hennie
    Replies:
    5
    Views:
    2,828
    Brock Allen
    Jun 4, 2005
  4. Bari Allen
    Replies:
    5
    Views:
    4,704
    Daniel Walzenbach
    Nov 20, 2005
  5. Pedro Vera
    Replies:
    5
    Views:
    438
    Juan T. Llibre
    Mar 30, 2007
Loading...

Share This Page