SQL classic asp - conditional statement

Question

I am not an expert at SQL and have indentified a bug in some existing classic asp code of mine such that it does not work in all situations. I am hopeful somebody with some excellent sql experience can help me with writing the more complex situation of when both situations occur such that

If intItemType = SV_ITEM_TYPE_MATRIX then this part of the SQL is used

LEFT OUTER JOIN usd_matrixSets MS " &_
    "ON MS.itemID = SI.itemID " &_

But when intItemType <> SV_ITEM_TYPE_MATRIX then it is ignored.

As you can see I have written code for either/or situations but this does not actually cover me for the situation of when both can occur .

Here is the code:

If intItemType = SV_ITEM_TYPE_MATRIX Then   
    strSQL = "SELECT SI.itemID, itemType, itemText, minimumValue, maximumValue, dataType, MS.matrixSetType, MS.matrixSetID " &_
             "FROM usd_SurveyItem SI " &_
             "LEFT OUTER JOIN usd_matrixSets MS " &_
             "ON MS.itemID = SI.itemID " &_
             "WHERE surveyID = " & intSurveyID &_
             " AND itemType " &_
             "In(" & SV_ITEM_TYPE_TEXTAREA & "," &_
                    SV_ITEM_TYPE_SINGLE_LINE & "," &_
                    SV_ITEM_TYPE_DATE & "," &_
                    SV_ITEM_TYPE_CHECKBOXES & "," &_
                    SV_ITEM_TYPE_RADIO & "," &_
                    SV_ITEM_TYPE_DROPDOWN & "," &_
                    SV_ITEM_TYPE_MATRIX & "," &_
                    SV_ITEM_TYPE_UPLOAD_FILE & ")" &_
             " AND pageID = " & intPageNumber &_
             " ORDER BY pageID, SI.orderByID "

        else            
    strSQL = "SELECT SI.itemID, itemType, itemText, minimumValue, maximumValue, dataType" &_
             " FROM usd_SurveyItem SI " &_
             "WHERE surveyID = " & intSurveyID &_
             " AND itemType " &_
             "In(" & SV_ITEM_TYPE_TEXTAREA & "," &_
                    SV_ITEM_TYPE_SINGLE_LINE & "," &_
                    SV_ITEM_TYPE_DATE & "," &_
                    SV_ITEM_TYPE_CHECKBOXES & "," &_
                    SV_ITEM_TYPE_RADIO & "," &_
                    SV_ITEM_TYPE_DROPDOWN & "," &_
                    SV_ITEM_TYPE_MATRIX & "," &_
                    SV_ITEM_TYPE_UPLOAD_FILE & ")" &_
             " AND pageID = " & intPageNumber &_
             " ORDER BY pageID, SI.orderByID "      

        end if

My problem is that sometimes I have both SV_ITEM_TYPE_MATRIX and others all at the same time within my database. How can I rewrite this code?

You should consider using command parameters: forums.asp.net/t/886691.aspx/1 — SQLMason
"GROUP BY" would do it - but which item type would you want to show? — SQLMason

Mark Brackett Mark Brackett · Accepted Answer · 2012-06-01T21:01:45

You should always do the LEFT JOIN, and just let the extra values (MS.matrixSetType and MS.matrixSetID) be NULL when it's not SV_ITEM_TYPE_MATRIX.

Something like (I'll use parameters to avoid SQL injection vulnerabilities):

SELECT 
    SI.itemID, itemType, itemText, minimumValue, maximumValue, dataType, 
    MS.matrixSetType, MS.matrixSetID 
FROM usd_SurveyItem SI
LEFT OUTER JOIN usd_matrixSets MS ON 
    MS.itemID = SI.itemID 
    AND @intItemType = SI.itemType
WHERE
    surveyID = @intSurveyID  
    AND itemType IN (
       ...
    )
    AND pageID = @intPageNumber 
ORDER BY 
    pageID, SI.orderByID

SQL classic asp - conditional statement

1 Answers