Dynamic WHERE and ORDER BY clauses

Results 1 to 3 of 3

Thread: Dynamic WHERE and ORDER BY clauses

  1. #1
    Wends Guest

    Default Dynamic WHERE and ORDER BY clauses

    Hi,<BR><BR>I was wondering if you could inform me if it is possible to create dynamic WHERE and ORDER BY clauses in an ASP script. I am obtaining the conditions data from a form but I am not sure how to create the SQl statement. I do not used stored procedures so I do not think I can use the COALESCE function as it says it is not defined!!<BR><BR>Please help if you have any ideas.<BR><BR>Many thanks<BR><BR>Wendy

  2. #2
    Scott S Guest

    Default RE: Dynamic WHERE and ORDER BY clauses

    Sure, just try something like this:<BR><BR>&#060;% <BR><BR>dim sqlstring<BR>dim param1 : param1 = request("param1")<BR>dim param2 : param2 = request("param2")<BR><BR><BR>sqlstring = "select * from table " <BR><BR>if param1 &#060;&#062; "" or param2 &#060;&#062; "" then &#039;if its not null/empty<BR>sqlstring = sqlstring & "where "<BR><BR>&#039;Now test the individual param<BR>if param1 &#060;&#062; "" then<BR>sqlstring = sqlstring & "somefield = &#039;" & param1 & "&#039;"<BR>end if <BR><BR>if param2 &#060;&#062; "" then<BR>sqlstring = sqlstring & "someotherfield = &#039;" & param2 & "&#039;"<BR>end if<BR><BR>End if<BR><BR>set recordset = objectconnection.execute(sqlstring)<BR><BR>%&#062; <BR><BR>this assumes your parammeters are coming from the forms or querystring collection but other than that its pretty simple.<BR><BR>Order by would work much in the same way.<BR><BR>Hope that Helps<BR><BR>Scott

  3. #3
    Scott S Guest

    Default RE: Dynamic WHERE and ORDER BY clauses

    OOPS in the code i forgot to include the AND, you&#039;ll need to put an and inbetween ever parammeter ie somefield = something AND someotherfield = something else. <BR><BR>if you load you params up into an array it should be pretty easy to figure out when there are no more & an AND is no longer necessary.

Posting Permissions

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