|
|
Posted by pbd22 on 07/16/07 01:33
On Jul 13, 3:04 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> pbd22 (dush...@gmail.com) writes:
> >> 1) and 3) makes perfect sense. The second I cannot understand. As far
> >> as I understand, this query is likely to return multiple rows. But which
> >> rows it returns - we don't know. Since @sort is NULL at this point,
> >> the ORDER BY has no effect. It's probably the explanation to why your
> >> @sort goes bad, but I can't say what you should do to correct, because
> >> I have very little clue how your tables are related.
>
> > It sounds to me like the middle code block is causing me my errors but
> > I am not sure what I am doing wrong still. Would you mind taking a look
> > at an Entity Relationship Diagram? It might give you a better
> > understanding of how my data is designed and for what purpose. If that
> > is OK, I'll email it to you via your address provided here.
>
> And I don't know what you are doing wrong, because I don't know what you
> are trying to achieve.
>
> There is a common recommendation for this type of questions, and that is
> that you post:
>
> o CREATE TABLE statements for your tables.
> o INSERT statements with sample data.
> o The resired result given the sample.
>
> Now, since your original query had some 7-8 tables whereof several repeated
> in the FROM clause, you will need to simplify the problem down to the
> core.
>
> If I understand this correctly, this is about saved searches, so the
> clou is certainly SavedSearches, but try to invent a similar case with
> fewer tables. Yes, that may take you some time, but I rather have
> you doing that than showing me an E-R diagramme that may not help me
> to understand what you are trying to achieve. To wit, I am not sure
> that you understand yourself. But if you spend some time with a simpler
> case then maybe you get can get that understanding.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks Erland.
OK, I have done what you said and reduced the tables used in the
search.
After much messing around with the stored procedure, I have figured
out that
by commenting out the following code (at the end of the procedure), I
can get
results:
WHERE
tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo--WHERE
(and, the ORDERBY code is commented out as it depends on this code).
I have also found that if I leave any one of the above lines the code
again
fails. So, for some reason, @gender, @country, @low, @high, and @photo
are not getting passed appropriately.
This is where I am at the moment, I'll report back as progress is
made.
Comments always appreciated (if you see something I don't) along the
way.
Thanks again for your patience.
Peter
[Back to original message]
|