Hey all,
I am trying to convert the following code into a stored procedure. I wonder if any of you could help me. It appears that it is saying if a string startswith "z_" then replace "z_" with "" and if Count is = 0 then use the first Where clause with the proceeding SQL statement else the second where clause.

For each item in Request.QueryString
                    if Left(item,2)="z_" Then
                        intCurrentVar = Replace(item, "z_", "")
                        if iCount = 0 Then
                            strWhereClause = " WHERE tblPCRDdata.Option_ID=" & intCurrentVar

                        else
                            strWhereClause = strWhereClause & " or tblPCRDdata.Option_ID=" & intCurrentVar
                        end if

                        End if

                    iCount = iCount + 1
                Next

               strReportSQL = "SELECT tblMain.*, tblAgency.Name AS Agency " _
                                & " FROM tblMain INNER JOIN " _
                                & " tblPCRDdata ON tblMain.ID = tblPCRDdata.Report_ID INNER JOIN " _
                              & " tblAgency ON tblMain.HHSAgency = tblAgency.ID " & strWhereClause

Code?  We don't need no stinkin' code!

Code?  We don't need no stinkin' code!

I would say this post better fits in an ASP or .NET forum, not in an SQL server 2000 forum, to get quick and better response. It may go with no response for a long time here.

I too wonder, if of us could help you at this forum.

Cheers.

_________________________
- Vijay G
Strive for Perfection

You could put this in a stored procedure with a little rewriting using dynamic SQL. Since the execution plan will have to be generated each time anyway, executing from client or server code will be the same.

Alternatively, since there are only two paths, you could write two stored procedures. This would avoid the execution plan generation on compile.

From the code above, there is something not right. The only difference appears to be the logical operator 'OR'. In path 1, the statement would filter on tblPCRDdata.Option_ID. In path 2, the statement would optionally filter, tblMain.HHSAgency = tblAgency.ID OR tblPCRDdata.Option_ID. That doesn't make sense to me. I may be missing the business logic completely.



Adam Gossage
Lake Wylie, SC, USA