ASP & Stored Procedures!

[Anthony Judd]

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]]

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

 

[Mike D]

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