Posted by pbd22 on 04/18/07 13:12

On Apr 13, 4:18 pm, "pbd22" <dush...@gmail.com> wrote:
> On Apr 12, 6:50 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
>
>
>
> > pbd22 wrote:
> > > select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
> > > tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
> > > tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
> > > tab2.photolocation , tab2.photoname , tab2.photodefault ,
> > > tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from
>
> > Why the hell doesn't anyone format their queries for readability?
>
> > > order by CASE WHEN userID=67 THEN 1 WHEN userID=103
> > > THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
> > > THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
> > > THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
> > > THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100
>
> > This should be driven by a Users table. Do you really want to mess
> > around with altering the query every time a user is added/removed?
>
> good point Ed. I didn't think of that. I'll obviously have to do that
> - yet
> another thing on the "to-do list". thanks...

Hi All.

So, I have taken all of your advice and turned my code into a stored
procedure that has a dynamic ORDER BY clause for user deletions.
This is turning into a bit of a beast and I am getting well out of my
comfort zone.

I have taken Hugo's advice and moved paging from the client to the
server (or, at least, tried to). I have based my sproc on the RowCount
code in the link he provided. I have also taken Ed's advice and made
my ORDER BY clause table-based for the CASE logic.

I have pasted the code below (sorry if i offend anybody about
formatting.
I am not sure how to format code here... suggestions?). When I use
Query Analyzer and step through the code in "debug" mode, the compiler
jumps to the following line:

----------------------------
SET ROWCOUNT 0
----------------------------

and throws this error:

---------------------------------------------------------------------------------------------

CurrentPage TotalPages TotalRows
----------- ----------- -----------
0 0 0

Server: Msg 507, Level 16, State 2, Procedure sp_SearchRowCount, Line
60
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
SET ROWCOUNT. Must be a non-null non-negative integer.

@RETURN_VALUE = -6
---------------------------------------------------------------------------------------------

Could somebody tell me what about the logic in my code is causing this
failure (I know that the value is null and that that is causing the
error). I have a feeling it might have to do with the following lines
of code that I have commented out in the WHERE clause (but, I am not
sure what it is doing or how to add the logic to my own code):

-- ArtistName + '~' + Title
-- >= @aname + '~' + @title


Other feedback appreciated also. I really appreciate your help!
Thanks.

---------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].[sp_SearchRowCount]
@pagenum INT = 1,
@perpage INT = 10
AS
BEGIN
SET NOCOUNT ON

DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT

SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from Photos with(nolock) union select distinct email_address
from Edit with(nolock) union select distinct email_address
from Searches with(nolock) union select distinct email_address
from Precedence with(nolock) union select distinct
email_address
from LoginDate with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress =
tab1.emailAddress)
Inner Join Precedence tab5 on tab5.UserID=tab1.UserID
Left Join Photos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LoginDate tab4 on (drv.emailAddress =
tab4.email_address)
Left Join Edit tab3 on (drv.emailAddress = tab3.email_address)
Left Join Searches tab6 on (drv.emailAddress =
tab6.email_address)

IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum < 1 SET @pagenum = 1
IF @pagenum > @pages SET @pagenum = @pages

SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)

SELECT

CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows

-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows

DECLARE

@gender VARCHAR(50),
@country VARCHAR(50),
@orderby INTEGER,
@low VARCHAR(50),
@high VARCHAR(50),
@photo VARCHAR(50),
@sort INTEGER

SET ROWCOUNT @lbound

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from Photos with(nolock) union select distinct email_address
from Edit with(nolock) union select distinct email_address
from Searches with(nolock) union select distinct email_address
from Precedence with(nolock) union select distinct
email_address
from LoginDate with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress =
tab1.emailAddress)
Inner Join Precedence tab5 on tab5.UserID=tab1.UserID
Left Join Photos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LoginDate tab4 on (drv.emailAddress =
tab4.email_address)
Left Join Edit tab3 on (drv.emailAddress = tab3.email_address)
Left Join Searches tab6 on (drv.emailAddress =
tab6.email_address)

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT @perPage

SELECT COALESCE
(
tab1.emailAddress,
tab2.user_name,
tab3.email_address,
tab4.email_address,
tab5.email_address,
tab6.email_address
)
id ,
tab1.bday_day ,
tab1.bday_month ,
tab1.bday_year ,
tab1.gender ,
tab1.zipCode ,
tab1.siteId ,
tab1.userID ,
tab2.photo_location ,
tab2.photo_name ,
tab2.photo_default ,
tab2.no_photo ,
tab3.headline ,
tab3.about_me ,
tab4.login_date ,
tab4.login_isonline,
tab5.up_order,
tab6.saved_orderby,
tab6.saved_sort,
tab6.saved_fage,
tab6.saved_tage

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from Photos with(nolock) union select distinct email_address
from Edit with(nolock) union select distinct email_address
from Searches with(nolock) union select distinct email_address
from Precedence with(nolock) union select distinct
email_address
from LoginDate with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress =
tab1.emailAddress)
Inner Join Precedence tab5 on tab5.UserID=tab1.UserID
Left Join Photos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LoginDate tab4 on (drv.emailAddress =
tab4.email_address)
Left Join Edit tab3 on (drv.emailAddress = tab3.email_address)
Left Join Searches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE

tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo

-- ArtistName + '~' + Title
-- >= @aname + '~' + @title

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT 0

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[Back to original message]