53 users online (0 members and 53 guests)  


  Results 1 to 8 of 8

Related

  1. Syntax for Select Statement    Forum: Databases
    Replies: 2
  2. Matching Case    Forum: PHP Forum
    Replies: 2
  3. MySQL Select statement    Forum: Databases
    Replies: 1
  4. Replies: 0
  1. #1
    dlongnecker's Avatar
    Tired Student...(Z_z)

    Status
    Offline
    Join Date
    Aug 2004
    Location
    Wichita, KS
    Posts
    86

    Select Case SQL Statement...

    I've been tasked with revamping an existing web interface for our elected officials page that isn't working (http://www.usd259.com/cer/).

    After tearing into the code... it was totally created in FrontPage, which makes me very violent. I'm in the process of converting it to a cleaner ASP format; however, have hit a snag with one of the SQL statements.

    They have the option to do the following:

    1. Query by site name
    and/or
    2. Query by ZIP code
    and/or
    3. Query by District (city council district)

    What would be the cleanest way to write the SQL statements? I fear writing huge select case statements to create a dynamic SQL statement (see example below). Do I have other options? Suggestions?

    ::Pseudo Code::

    Code:
    strSQL = "SELECT DISTINCT (*) FROM [Site Master] "
    
    Select Case ZIPCODE
    Case 0 ' No
    strSQL = strSQL & ""
    
    Case 1 ' Yes
    strSQL = strSQL & "WHERE [Site ZIP] = '" & strZIP & "'"
    End Select
    What to do if they select DISTRICT, but not ZIP CODE? Can SQL Statements have multiple WHERE's? Am I stuck on a concept that is better handled another way? (No PHP cracks... (^_~) I don't control the server this is going on.)

    *blank stare*

    Fridays should be gaming days, not coding days... *le sigh*

    TIA!!

    -David

  2. #2
    Doorknob's Avatar
    Super Newbie

    Status
    Offline
    Join Date
    Jun 2002
    Location
    Malaysia
    Posts
    316

    Re: Select Case SQL Statement...

    bWhere = false
    strSQL = "SELECT DISTINCT (*) FROM [Site Master] "

    Select Case ZIPCODE
    Case 0 ' No
    if bWhere then
    strSQL = strSQL & "WHERE [Site District]='" & strDistrict & "'"
    else
    strSQL = strSQL & "AND [Site District]='" & strDistrict & "'"
    bWhere = true
    end if

    Case 1 ' Yes
    if bWhere then
    strSQL = strSQL & "AND [Site ZIP] = '" & strZIP & "'"
    else
    strSQL = strSQL & "WHERE [Site ZIP] = '" & strZIP & "'"
    bWhere = true
    end if
    End Select

  3. #3
    shivraj's Avatar
    New User

    Status
    Offline
    Join Date
    Jul 2006
    Posts
    1

    Select Case SQL Statement...

    i want coplete tutorials for case statement in sql2000

  4. #4
    yoemanspiffy's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Aug 2006
    Posts
    28

    Re: Select Case SQL Statement...

    I realize this is a bit late (by 2 years it looks like), but I gotta write something on this.
    Your BEST solution to this is to strip ALL the SQL code out of your ASP pages, period. This will be a far elegant solution and everyone will love you for it later on.
    Lets say you have to dynamically build the query string based upon user entries for their address, you are going to first create a stored procedure that will take all of the variables you need to pass in order to generate AND execute your query. (Keep all the database stuff in the database)
    The idea here is to get the SQL string making completely out of your ASP page, so you don't have to piddle with it in ASP, but you can work in the native SQL. (I am going to assume MSSQL for the DB, although now MYSQL supports SPs as well).
    The first thing I would do is to create your SQL stored procedure that is going to do all the CASE work for you. It would look something like this:

    DROP PROCEDURE mySiteSP_generateNexecDynamic
    GO
    CREATE PROCEDURE mySiteSP_generateNexecDynamic
    @thisZIP NVARCHAR(254) = NULL,
    @thisDIS NVARCHAR(254) = NULL,
    @thisQuery NVARCHAR(254) = "SELECT DISTINCT (*) FROM [Site Master] ",
    @strApnd NVARCHAR(254) = 'WHERE'
    AS
    IF @thisZIP IS NOT NULL
    BEGIN
    SET @thisQuery = @thisQuery + 'WHERE [Site ZIP] =' + @thisZIP
    END
    IF @thisDIS IS NOT NULL
    BEGIN
    IF @thisZIP IS NOT NULL
    BEGIN
    SET @strApnd = 'AND'
    END
    SET @thisQuery = @thisQuery + ' ' + @strApnd + ' [Site District] =' + @thisDIS
    END
    EXEC sp_executeSQL @thisQuery;
    GO

    Hopefully I didn't just blow your mind. now how the heck do you use it?
    What we just did was to create a stored procedure that takes two values: the zipcode and the district. If either is null, the SQL code (notice I said 'SQL' code) picks that up and adjusts the end query string for you. Then it executes the query string that it just built for you dynamically generating a set of rows.
    How the heck do you run it? Good question:
    Here is the SQL statement (string to put into your DB call) to run it:

    "EXEC mySiteSP_generateNexecDynamic @thisZIP = "&strZIP&", @thisDIS="&strDistrict
    if your strZIP OR strDistrict are NULL, you set the string to "NULL"

    In this case there is no need for a CASE in either ASP or the SQL as it is a simple switch.
    Last edited by yoemanspiffy; 08-15-2006 at 06:00 PM.

  5. #5
    yoemanspiffy's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Aug 2006
    Posts
    28

    Re: Select Case SQL Statement...

    This is actually a little cleaner:

    DROP PROCEDURE mySiteSP_generateNexecDynamic
    GO
    CREATE PROCEDURE mySiteSP_generateNexecDynamic
    @thisZIP NVARCHAR(254) = NULL,
    @thisDIS NVARCHAR(254) = NULL,
    @thisQuery NVARCHAR(254) = "SELECT DISTINCT (*) FROM [Site Master] ",
    @strApnd NVARCHAR(254) = 'WHERE'
    AS
    IF @thisZIP IS NOT NULL
    BEGIN
    SET @thisQuery = @thisQuery + 'WHERE [Site ZIP] =' + @thisZIP
    SET @strApnd = 'AND'
    END
    IF @thisDIS IS NOT NULL
    BEGIN
    SET @thisQuery = @thisQuery + ' ' + @strApnd + ' [Site District] =' + @thisDIS
    END
    EXEC sp_executeSQL @thisQuery;
    GO

  6. #6
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: Select Case SQL Statement...

    Can you guys point me to a good tutorial for getting to grips with SQL code, please?

  7. #7
    yoemanspiffy's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Aug 2006
    Posts
    28

    Re: Select Case SQL Statement...

    http://www.w3schools.com/sql/ is a good start.
    To be honest, what I did was to buy the SQL Bible and shut myself in my office for a weekend. I went through it step by step physically creating each example. I tell you, it really helped, now I can sling that stuff. WHat I had done before was to build only what I needed for each application. Once I got my head around all the goodies that coding time got cut so dramatically that now I actually look forward to the DB part!
    Maybe we can get DAVE to start a SQL forum here (Hint Hint, Listening Dave?). I wuold be happy to contribute what I can. I stare at the stuff all day long (Well, when I am not staring at my wife that is!)
    YM

  8. #8
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: Select Case SQL Statement...

    Seconded