|
|
Posted by Erland Sommarskog on 07/18/07 21:28
pbd22 (dushkin@gmail.com) writes:
> OK. I have changed the procedure significantly to use the Row_Number()
> method in SQL 2005 for paging.
>
> In this procedure, I am trying to do the following:
>
> 1) used the passed-in parameters to figure out which saved search we
> are using.
> 2) query the SavedSearch table to populate the local parameters with
> the saved values
> 3) create a temporary table that is sorted against the local
> paramerters.
>
> I am having problems figuring out how to create this temporary table.
> At a quick glance, does the "SELECT COALESCE" statement seem
> like it has been logically placed or does it seem out of place? I keep
> getting
>
> "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. "
This is because you don't have an alias does the derived table. Add
"AS x" before the faulty parenthesis.
Also, you are missing a semi-colon before ';'
> I can't seem to build the temp table without errors.
I can't even see a temp-table. I can see a common table expression,
is that you are thinking of?
> At a quick glance does the logic in this procedure seem to make sense?
Since you apparently haven't tested the code yet, I don't feel compelled
to make a thorough review. But:
> SET @saveddate = (SELECT saved_date FROM SavedSearches WHERE
> search_name=@searchname AND email_address=@emailaddy)
> SET @savedname = (SELECT saved_name FROM SavedSearches WHERE
> search_name=@searchname AND email_address=@emailaddy)
>...
It would be more effecient and less verbose with:
SELECT @saveddate = saved_date, @savedname = saved_name, ...
FROM SavedSearches
WHERE search_name=@searchname
AND email_address=@emailaddy
Really what the CTE that returns a single column is supposed to mean,
I don't know, but I guess that you find out when you test what you
really intended.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|