Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Paging! Paging! Paging! AGAIN??? Yes!

Author  Topic 

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-07-30 : 18:07:08
Hey guys and gals, I'm trying to get paging to work on a few sprocs and I read all over this site about paging and this one 4-paged post, but I have probably the simplest question concerning paging. Instead of me assuming (since I'm fairly new) I prefer to ask.

For starters, I recently learned that 'return' in a sproc is usually used for returning any errors and returns an INT (numbers between -2,147,483,648 and 2,147,483,647). And I usually like to have return @@rowcount on a sproc, although since my knew knowledge, I think that would be bad practice, especially if, can be possible, a query returns more than 2,147,483,647 records (just for example sakes), then return will not work for me, I assume it would be better off to set it to an output variable, no?

Ok now that we got that straight, my question is how to determine the number of total records for a given query, when using Jeff's ([url]http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx[/url]) method. My first thought would be @@rowcount, but wouldn't that give you the number of rows processed in the query when, let's say, you set rowcount to 20, you would get 20, right? My next idea is to, i guess, have two queries, one with select count(*) from tableA and any where clauses, that would give you the number of rows for that query, then you run the query with the results based on the page you want. (I assume.)

SQL yukon taking to long to get here

- RoLY roLLs

Kristen
Test

22859 Posts
Posted - 2004-07-31 : 02:15:00
OUTPUT variable, yes. Or put it in the resultset (if there is one).

You could "pull" the matching rows into a temporary table, sorted in "viewing order". That would give you the RowCount.

And then display based on "from row N to row M".

I reckon the best way to do this is to have a temporary table with IDENTITY and just the Primary Keys of the recordset. Pull the [PKs of the] appropriate records into the temporary table, in sorted order.

Next JOIN the temporary table to the real table, and any other "supporting cast" tables to get the data you need in the select, using the IDENTITY column to restrict to rows N through M.

This is obviously less efficient that Jeffs method; on the plus side we impliment this in a User Defined Function and are then able to use it in multiple SProcs (Cant' do that with Jeffs method because your cannot use SET ROWCOUNT in a UDF).

Kristen
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-07-31 : 15:52:19
quote:
Originally posted by Kristen

OUTPUT variable, yes. Or put it in the resultset (if there is one).

like this?
CREATE procedure proc_SelectClientByClientIDNum
	@ClientIDNum varchar(13)
	, @RowCount as bigint output 
as
begin
	select
		@RowCount = count(Client.ClientIDNum)
	from
		Client
	where
		(Client.ClientIDnum like '%' + @ClientIDNum + '%')
	
	select
		Client.ClientIDNum
		, dbo.DeFormatGUID(Client.ClientUID) as ClientUID
		, Client.ClientName
	from
		Client
	where
		(Client.ClientIDnum like '%' + @ClientIDNum + '%')
	order by
		Client.ClientName
	
	return @@error
end
GO

If the temp table is better, can you give me a simple example of how to use it with the above statement? Thanks!

- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-07-31 : 15:58:49
btw: pk column is: [ClientID] [int] IDENTITY (1, 1)

- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-07-31 : 16:20:09
Well i tried using the previous code I posted, but I don't know how to get the @RowCount Variable in my asp when I have that second select statement. Here's my code:

<%
	Set cmd = Server.CreateObject("ADODB.Command")
	With cmd
		.ActiveConnection = dbc
		.CommandText = "proc_SelectClientByClientIDNum"
		.CommandType = adCmdStoredProc
		
		.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
		.Parameters.Append .CreateParameter("@ClientIDNum", adVarChar, adParamInput, 13, Form_ClientIDNum)
		.Parameters.Append .CreateParameter("@RowCount", adBigInt, adParamOutput, 8)
	End With

	Set rsClient = Server.CreateObject("ADODB.Recordset")
	Set rsClient = cmd.Execute 'Notice this line for next post
	
	fncRowCount = cmd("@RowCount")
	
	fncClientList = ""
	If NOT rsClient.BOF AND NOT rsClient.EOF Then
		
		Do While NOT rsClient.EOF
			fncClientUID = rsClient("ClientUID")
			fncClientIDNum = rsClient("ClientIDNum")
			fncClientName = rsClient("ClientName")
			
			fncClientList = fncClientList & fncClientIDNum & " - " & fncClientName & "<br>"

			rsClient.MoveNext
		Loop
	Else
			fncClientList = "Sorry! No results."
	End If
	
	response.write fncClientList
	response.write "<br>" & fncRowCount 'For Testing purposes just print it out
		
	Set rsClient = Nothing
	Set cmd = Nothing
%>


- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-07-31 : 16:32:33
UPDATE: The only way I know how to get an output param with the way I execute the sproc is:
	Set cmd = Server.CreateObject("ADODB.Command")
	With cmd
		.ActiveConnection = dbc
		.CommandText = "proc_SelectClientByClientIDNum"
		.CommandType = adCmdStoredProc
		
		.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
		.Parameters.Append .CreateParameter("@ClientIDNum", adVarChar, adParamInput, 13, Form_ClientIDNum)
		.Parameters.Append .CreateParameter("@RowCount", adBigInt, adParamOutput, 8)
		
		.Execute 
	End With


and to get the recordset is the way i have it before without the .execute but instead:
	Set rsClient = Server.CreateObject("ADODB.Recordset")
	Set rsClient = cmd.Execute

But with this second one, cmd("@Rowcount") is nothing since we set it to the rsClient, right? (not too sure about the technicalities at this part). So what I did was combined them both to give me:
<%
	Set cmd = Server.CreateObject("ADODB.Command")
	With cmd
		.ActiveConnection = dbc
		.CommandText = "proc_SelectClientByClientIDNum"
		.CommandType = adCmdStoredProc
		
		.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
		.Parameters.Append .CreateParameter("@ClientIDNum", adVarChar, adParamInput, 13, Form_ClientIDNum)
		.Parameters.Append .CreateParameter("@RowCount", adBigInt, adParamOutput, 8)
		
		.Execute
	End With

	Set rsClient = Server.CreateObject("ADODB.Recordset")
	Set rsClient = cmd.Execute
	
	fncRowCount = cmd("@RowCount")
	
	fncClientList = ""
	If NOT rsClient.BOF AND NOT rsClient.EOF Then
		
		Do While NOT rsClient.EOF
			fncClientUID = rsClient("ClientUID")
			fncClientIDNum = rsClient("ClientIDNum")
			fncClientName = rsClient("ClientName")
			
			fncClientList = fncClientList & fncClientIDNum & " - " & fncClientName & "<br>"

			rsClient.MoveNext
		Loop
	Else
			fncClientList = "Sorry! No results."
	End If
	
	response.write fncClientList
	response.write "<br>" & fncRowCount 'For Testing purposes just print it out
		
	Set rsClient = Nothing
	Set cmd = Nothing
%>

But I feel this is not good, since it calls the sproc twice, right? Well if anyone has some sample code to help me work this out to get a recordset along with an output variable with adodb/asp/mssql, it would be great! Thanks to all who can help.


- RoLY roLLs
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts
Posted - 2004-08-01 : 05:46:24
No, you don't need to execute the proc twice. All you need to do is execute the proc, and ADO takes care of filling in values in all the output parameters and returning the recordset, all from one function call. So, the first bit of your code would change to:

	Set cmd = Server.CreateObject("ADODB.Command")
	Set rsClient = Server.CreateObject("ADODB.Recordset")

	With cmd
		.ActiveConnection = dbc
		.CommandText = "proc_SelectClientByClientIDNum"
		.CommandType = adCmdStoredProc
		
		.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
		.Parameters.Append .CreateParameter("@ClientIDNum", adVarChar, adParamInput, 13, Form_ClientIDNum)
		.Parameters.Append .CreateParameter("@RowCount", adBigInt, adParamOutput, 8)
		
		Set rsClient = .Execute()
	End With


OS
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 00:25:11
is this not the same as my original?
<%
	Set cmd = Server.CreateObject("ADODB.Command")
	With cmd
		.ActiveConnection = dbc
		.CommandText = "proc_SelectClientByClientIDNum"
		.CommandType = adCmdStoredProc
		
		.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
		.Parameters.Append .CreateParameter("@ClientIDNum", adVarChar, adParamInput, 13, Form_ClientIDNum)
		.Parameters.Append .CreateParameter("@RowCount", adBigInt, adParamOutput, 8)
	End With

	Set rsClient = Server.CreateObject("ADODB.Recordset")
	Set rsClient = cmd.Execute
	
	fncRowCount = cmd("@RowCount") 'this does not work when I try to get the falue of @RowCount
	
	fncClientList = ""
	If NOT rsClient.BOF AND NOT rsClient.EOF Then
		
		Do While NOT rsClient.EOF
			fncClientUID = rsClient("ClientUID")
			fncClientIDNum = rsClient("ClientIDNum")
			fncClientName = rsClient("ClientName")
			
			fncClientList = fncClientList & fncClientIDNum & " - " & fncClientName & "<br>"

			rsClient.MoveNext
		Loop
	Else
			fncClientList = "Sorry! No results."
	End If
	
	response.write fncClientList
	response.write "<br>" & fncRowCount 'For Testing purposes just print it out
		
	Set rsClient = Nothing
	Set cmd = Nothing
%>


- RoLY roLLs
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts
Posted - 2004-08-02 : 03:39:09
>>fncRowCount = cmd("@RowCount") 'this does not work when I try to get the falue of @RowCount

Ah, that is a documented issue with server-side forward-only recordsets (the default) and output parameters. The value of a return or output parameter is not available until the returned recordset is closed. The simple solution is to convert it to a client-side cursor:

	Set rsClient = Server.CreateObject("ADODB.Recordset")
	rsClient.CursorLocation = adUseClient
	Set rsClient = cmd.Execute




OS
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 12:35:28
ah, thanks. Lemme ask this then, when is better to use a server-side or client-side cursor (ie:web app, app only used in the same ficility as the sql server's location, etc)

Thanks.

- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 12:52:35
BTW: I just tried the client side location, and still does not work :( also, the link u provided does not exist. here's my code so far:

sproc:
CREATE procedure proc_SelectClientByClientIDNum
	@ClientIDNum varchar(13)
	, @RowCount as bigint output
as
begin
	select
		@RowCount = count(Client.ClientIDNum)
	from
		Client
	where
		(Client.ClientIDNum like '%' + @ClientIDNum + '%')
	
	select
		Client.ClientIDNum
		, dbo.DeFormatGUID(Client.ClientUID) as ClientUID
		, Client.ClientName
	from
		Client
	where
		(Client.ClientIDNum like '%' + @ClientIDNum + '%')
	order by
		Client.ClientName
	
	return @@error
end
GO


asp:
<%
	Set cmd = Server.CreateObject("ADODB.Command")
	With cmd
		.ActiveConnection = dbc
		.CommandText = "proc_SelectClientByClientIDNum"
		.CommandType = adCmdStoredProc
		
		.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
		.Parameters.Append .CreateParameter("@ClientIDNum", adVarChar, adParamInput, 13, Form_ClientIDNum)
		.Parameters.Append .CreateParameter("@RowCount", adBigInt, adParamOutput, 8)
	End With
	
	Set rsClient = Server.CreateObject("ADODB.Recordset")
	rsClient.CursorLocation = adUseClient
	Set rsClient = cmd.execute
	
	fncRowCount = cmd("@RowCount")
	
	fncClientList = ""
	If NOT rsClient.BOF AND NOT rsClient.EOF Then
		
		Do While NOT rsClient.EOF
			fncClientUID = rsClient("ClientUID")
			fncClientIDNum = rsClient("ClientIDNum")
			fncClientName = rsClient("ClientName")
			
			fncClientList = fncClientList & fncClientIDNum & " - " & fncClientName & "<br>"

			rsClient.MoveNext
		Loop
	Else
			fncClientList = "Sorry! No results."
	End If
	
	response.write fncClientList
	response.write "<br>" & fncRowCount 'For Testing purposes just print it out
		
	Set rsClient = Nothing
	Set cmd = Nothing
%>


- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 14:56:18
Ok here's what I have done so far:

After the loop was done, I set the fncRowCount variable to the output param cmd("@RowCount") and it worked fine. But I wish to get the value before the loop. Is this possible? If so, how?



- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 16:04:43
Hye OS! Thanks to your small piece of advice about adUseClient, I looked around a bit and tried to use the cursor location defined to the connection instead of the recordset and it worked.

But I have a few questions concerning cursor location and will start a new post about it. Thanks!

- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 19:50:29
Hey Kristen, I was interested in adopting that function you have for paging, you said you use, if you don't mind, of course. How do you get it to work with more than one sproc? I assume u send the sql statement as a param?

Thanks.

- RoLY roLLs
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts
Posted - 2004-08-02 : 20:01:53
FYI-SQL Server 2005 version got Integrated support for paging.
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 20:13:44
Thanks for the update, actually already read it on an article. Thanks tho.

- RoLY roLLs
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-02 : 20:45:19
quote:
Originally posted by RoLYroLLs

Hey Kristen, I was interested in adopting that function you have for paging, you said you use, if you don't mind, of course. How do you get it to work with more than one sproc? I assume u send the sql statement as a param?

Thanks.

- RoLY roLLs

Also, kristen, do you make one function for each table you search? Hmm, kinda lost here. Thanks.

- RoLY roLLs
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts
Posted - 2004-08-03 : 02:52:08
Sorry about that Roly, I forgot, Snitz has a bug that eats up the semi-colons in URLs! MS KB articles always semi-colons in the URL, so I'll entrust this to the folks over at tinyurl... this is the link i was trying to point you to: http://tinyurl.com/3l3ot

And yeah sorry about the connection/recordset property setting, the CursorLocation property is supposed to be set on the Connection and not the recordset. Good to see you got it working yourself!

OS
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2004-08-03 : 11:09:05
moh, thanks for the link, I actually found that myself after you told me about it and searched around a bit , but I wouldn't have done it without your comments, thanks again.

- RoLY roLLs
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site