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...

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

On 18 Apr 2007 06:12:44 -0700, pbd22 wrote:

>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):

Hi pbd22,

Since those commented lines are *AFTER* the place where the error occurs
(the debugger jumps to the incorrect line; the only line that can cause
this error is either this one:

> SET ROWCOUNT @lbound

or this one:

> SET ROWCOUNT @perPage

I assume that it's the first, and that it's cause by some unexpected
things going on in the calculations neede to compute @lbound.

You already have added a great statement to aid in debugging; I'm gonna
ask you to add a bit more to it - so please change this:

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

to this:

SELECT

CurrentPage = @pagenum,
PageSize = @perpage,
TotalPages = @pages,
TotalRows = @rows,
UpperBoundary = @ubound,
LowerBoundary = @lbound

And then rerun the query and post back the results. We'll then have to
take it from there.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Apr 18, 8:08 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 18 Apr 2007 06:12:44 -0700, pbd22 wrote:
>
>
>
> >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):
>
> Hi pbd22,
>
> Since those commented lines are *AFTER* the place where the error occurs
> (the debugger jumps to the incorrect line; the only line that can cause
> this error is either this one:
>
> > SET ROWCOUNT @lbound
>
> or this one:
>
> > SET ROWCOUNT @perPage
>
> I assume that it's the first, and that it's cause by some unexpected
> things going on in the calculations neede to compute @lbound.
>
> You already have added a great statement to aid in debugging; I'm gonna
> ask you to add a bit more to it - so please change this:
>
> > SELECT
>
> > CurrentPage = @pagenum,
> > TotalPages = @pages,
> > TotalRows = @rows
>
> to this:
>
> SELECT
>
> CurrentPage = @pagenum,
> PageSize = @perpage,
> TotalPages = @pages,
> TotalRows = @rows,
> UpperBoundary = @ubound,
> LowerBoundary = @lbound
>
> And then rerun the query and post back the results. We'll then have to
> take it from there.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Hi Hugo,

Thanks for your reply. I have made the changes you suggested and got
the following output:

CurrentPage PageSize TotalPages TotalRows UpperBoundary
LowerBoundary
-----------........ ----------- ...... ----------- .....
----------- ........... ------------- .......-------------
0 ........ 10 .......... 0 .....
0 ............ 0 ....... -9



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

On 18 Apr 2007 09:10:16 -0700, pbd22 wrote:

(snip)
>Thanks for your reply. I have made the changes you suggested and got
>the following output:
>
>CurrentPage PageSize TotalPages TotalRows UpperBoundary
>LowerBoundary
>-----------........ ----------- ...... ----------- .....
>----------- ........... ------------- .......-------------
>0 ........ 10 .......... 0 .....
>0 ............ 0 ....... -9
>
>
>
>Server: Msg 507, Level 16, State 2, Procedure sp_PeopleSearch, Line 63
>[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
>SET ROWCOUNT. Must be a non-null non-negative integer.

Hi pbd22,

Thanks. This makes a lot clear.

Obviously, there are no rows returned by your query. Or to be more
precies - by the first copy of your query. You will have noted that you
have multiple copies of the same query in the procedure; the first copy
uses "SELECT @rows = COUNT(*), @pages = COUNT(*) / @perpage" to find the
total number of rows in the query. Since @rows is only set here, and it
is shown to be 0 in the results above, the query obviously returns no
rows. That means that either the query you want to run in a paged mode
does in fact not return any rows, or that you made a mistake when making
the extra copies of this query.

After this row-counting, some calculations are done:

(1) IF @rows % @perpage != 0 SET @pages = @pages + 1
(2) IF @pagenum < 1 SET @pagenum = 1
(3) IF @pagenum > @pages SET @pagenum = @pages

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

Line 1: The value of @perpage is rounded down in the query; this line
corrects that to rounding up. I'd have done that a little differently,
but that's mostly a matter of taste, and it works.

Line 2: In case someone passed a page number of zero or less to the
procedure, set it to 1. This is to prevent erroneous results.

Line 3: Another sanity check - if the page number passed to the proc
exceeds the actual number of pages, set it to the highest actual page
number. In this case, with 0 rows, this sets the page number to 0;
exactly the value that line 2 attempts to prevent!! This is the source
of the error you received.

Lines 4 and 5: Based on the page number, calculate the number of the
first and the last row to display. For page number 1, these numbers
would have been 1 and 10. For page number 0, the calculation yields -9
and 0. As a result, the proc later tries to set a negative ROWCOUNT.

I _think_ that you can easily fix this by swapping lines 2 and 3. I have
not checked every bit of the proc, but I suggest that you simply try it
and see what happens. If this results in (different) problems, you'll
have to explicitly add code to handle the possibility of an empty result
set.

I have already sent a mail to the author of the aspfaq article I refered
you to earlier, asking him to correct this page.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Apr 18, 11:23 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 18 Apr 2007 09:10:16 -0700, pbd22 wrote:
>
> (snip)
>
> >Thanks for your reply. I have made the changes you suggested and got
> >the following output:
>
> >CurrentPage PageSize TotalPages TotalRows UpperBoundary
> >LowerBoundary
> >-----------........ ----------- ...... ----------- .....
> >----------- ........... ------------- .......-------------
> >0 ........ 10 .......... 0 .....
> >0 ............ 0 ....... -9
>
> >Server: Msg 507, Level 16, State 2, Procedure sp_PeopleSearch, Line 63
> >[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
> >SET ROWCOUNT. Must be a non-null non-negative integer.
>
> Hi pbd22,
>
> Thanks. This makes a lot clear.
>
> Obviously, there are no rows returned by your query. Or to be more
> precies - by the first copy of your query. You will have noted that you
> have multiple copies of the same query in the procedure; the first copy
> uses "SELECT @rows = COUNT(*), @pages = COUNT(*) / @perpage" to find the
> total number of rows in the query. Since @rows is only set here, and it
> is shown to be 0 in the results above, the query obviously returns no
> rows. That means that either the query you want to run in a paged mode
> does in fact not return any rows, or that you made a mistake when making
> the extra copies of this query.
>
> After this row-counting, some calculations are done:
>
> (1) IF @rows % @perpage != 0 SET @pages = @pages + 1
> (2) IF @pagenum < 1 SET @pagenum = 1
> (3) IF @pagenum > @pages SET @pagenum = @pages
>
> (4) SET @ubound = @perpage * @pagenum
> (5) SET @lbound = @ubound - (@perpage - 1)
>
> Line 1: The value of @perpage is rounded down in the query; this line
> corrects that to rounding up. I'd have done that a little differently,
> but that's mostly a matter of taste, and it works.
>
> Line 2: In case someone passed a page number of zero or less to the
> procedure, set it to 1. This is to prevent erroneous results.
>
> Line 3: Another sanity check - if the page number passed to the proc
> exceeds the actual number of pages, set it to the highest actual page
> number. In this case, with 0 rows, this sets the page number to 0;
> exactly the value that line 2 attempts to prevent!! This is the source
> of the error you received.
>
> Lines 4 and 5: Based on the page number, calculate the number of the
> first and the last row to display. For page number 1, these numbers
> would have been 1 and 10. For page number 0, the calculation yields -9
> and 0. As a result, the proc later tries to set a negative ROWCOUNT.
>
> I _think_ that you can easily fix this by swapping lines 2 and 3. I have
> not checked every bit of the proc, but I suggest that you simply try it
> and see what happens. If this results in (different) problems, you'll
> have to explicitly add code to handle the possibility of an empty result
> set.
>
> I have already sent a mail to the author of the aspfaq article I refered
> you to earlier, asking him to correct this page.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis


Hugo -

Good catch! I never would have found that. That was spot on - I am
sure
the author of the article will appreciate the correction. So, I got
these results
with the adjustment (the formatting is sloppy but the lines at the
bottom are supposed to be under each column title from the query's
results):

CurrentPage PageSize TotalPages TotalRows UpperBoundary
LowerBoundary
----------- ----------- ----------- ----------- -------------
-------------
1 10 0 0 10 1

id
bday_day
bday_month
bday_year
gender
zipCode
siteId userID
photo_location
photo_name photo_default
no_photo headline
about_me
login_date login_isonline
up_order saved_orderby
saved_sort
saved_fage
saved_tage
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
-------------------------------------------------- -----------
----------------------------------------------------------------------------------------------------
-------------------------------------------------- -------------
-------- --------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ --------------
----------- -------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------


@RETURN_VALUE = 0


MY final question is - why don't I get any data returned with the
query? Shouldn't I be seeing results with the column headers? Or, is
this just to show that the query works in debug mode? Sorry - still
trying to learn my way around stored procedures.

Thanks again for your help.
Peter

On 18 Apr 2007 11:57:53 -0700, pbd22 wrote:

(snip)
>MY final question is - why don't I get any data returned with the
>query? Shouldn't I be seeing results with the column headers? Or, is
>this just to show that the query works in debug mode? Sorry - still
>trying to learn my way around stored procedures.

Hi Peter,

You're probably going to hate me for this, but my best guess is that
your tables simply don't contain any data that matches the criteria in
the trigger. I already hinted at this in my previous reply (though I
left a back door open for the possibilty that one of the queries
contained a copy/paste error).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Ah, yes. You were right Hugo. It was a problem with my select
statement.

Thanks again for your help, MUCH appreciated!

Cheers.
Peter

Hugo Kornelis wote:
> On 18 Apr 2007 11:57:53 -0700, pbd22 wrote:
>
> (snip)
> >MY final question is - why don't I get any data returned with the
> >query? Shouldn't I be seeing results with the column headers? Or, is
> >this just to show that the query works in debug mode? Sorry - still
> >trying to learn my way around stored procedures.
>
> Hi Peter,
>
> You're probably going to hate me for this, but my best guess is that
> your tables simply don't contain any data that matches the criteria in
> the trigger. I already hinted at this in my previous reply (though I
> left a back door open for the possibilty that one of the queries
> contained a copy/paste error).
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis