getting return from srtored procedure

hi everyone i am very very new to the sql server (2 days actually and ) so
far i learned creating SPs etc but couldnt workout how to get return value
from my prodecure

my sp as follows
________________________
CREATE PROCEDURE [dbo].[page_all_artists]
@PageIndex INT,
@PageSize INT,
@Total INT OUTPUT
AS
BEGIN
WITH Entries AS (
SELECT ROW_NUMBER() OVER (ORDER BY ArtistID asc)
AS Row, Artist, SongCount
FROM artists_table)
SELECT Row, Artist, SongCount
FROM Entries
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize


-----------------THIS ONE TO GET TOTAL RECORD COUNT AND I HAVE NO IDE HOW
IT SHOULD WORK---------------------

Select @total=Count(ArtistID) from (Select ArtistID From Entries e group by
e.ArtistID) b;

END
___________________________________________________

and this is the asp page that i am usign to get record set
___________________________________________________

<%
objConn ="Provider=SQLOLEDB.1;" & _
"Data Source=MYSERVER;" & _
"Initial Catalog=MYdb;" & _
"User ID=MYID;" & _
"Password=MYPW"
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "page_all_artists " & startFrom & "," & perPage
objRS.Open strSQL, objConn

Do While Not objRS.EOF %>

<%=objRS("Artist")%>

<%
objRS.MoveNext
Loop
objRS.Close
Set objRS=Nothing
Set objConn=Nothing
%>


____________________________________________________________ ____

can somebody show me how can i get @Total value in to my asp code so i can
calculate paging.

Thanks in advance

Re: getting return from srtored procedure

"noLoveLusT" wrote in message
news:Qv2fh.86770$bz5.47255@fe3.news.blueyonder.co.uk...
> hi everyone i am very very new to the sql server (2 days actually and ) so
> far i learned creating SPs etc but couldnt workout how to get return
> value from my prodecure
>
> my sp as follows
> ________________________
> CREATE PROCEDURE [dbo].[page_all_artists]
> @PageIndex INT,
> @PageSize INT,
> @Total INT OUTPUT
> AS
> BEGIN
> WITH Entries AS (
> SELECT ROW_NUMBER() OVER (ORDER BY ArtistID asc)
> AS Row, Artist, SongCount
> FROM artists_table)
> SELECT Row, Artist, SongCount
> FROM Entries
> WHERE Row between
> (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
>
>
> -----------------THIS ONE TO GET TOTAL RECORD COUNT AND I HAVE NO IDE HOW
> IT SHOULD WORK---------------------
>
> Select @total=Count(ArtistID) from (Select ArtistID From Entries e group
> by e.ArtistID) b;
>
> END
> ___________________________________________________
>
> and this is the asp page that i am usign to get record set
> ___________________________________________________
>
> <%
> objConnstr ="Provider=SQLOLEDB.1;" & _
> "Data Source=MYSERVER;" & _
> "Initial Catalog=MYdb;" & _
> "User ID=MYID;" & _
> "Password=MYPW"
> Set objRS = Server.CreateObject("ADODB.Recordset")
> strSQL = "page_all_artists " & startFrom & "," & perPage
> objRS.Open strSQL, objConn
>
> Do While Not objRS.EOF %>
>
> <%=objRS("Artist")%>
>
> <%
> objRS.MoveNext
> Loop
> objRS.Close
> Set objRS=Nothing
> Set objConn=Nothing
> %>
>
>
> ____________________________________________________________ ____
>
> can somebody show me how can i get @Total value in to my asp code so i can
> calculate paging.
>

Your procedure creates two recordsets that are returned at the same time.
The contents of the second one can be accessed through the NextRecordSet
method:

<%
Set ObjConn = Server.CreateObject("ADODB.Connection")
objConnStr ="Provider=SQLOLEDB.1;" & _
"Data Source=MYSERVER;" & _
"Initial Catalog=MYdb;" & _
"User ID=MYID;" & _
"Password=MYPW"
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open objConnStr
objConn.page_all_artists startFrom, perPage, objRS

Do While Not objRS.EOF
Response.Write objRS("Artist") & "
"
objRS.MoveNext
Loop

Set objRS = objRS.NextRecordSet
Response.Write "Total records: " & objRS(0)

objRS.Close
Set objRS=Nothing
Set objConn=Nothing
%>

Assuming you would actually want to know the total number of records before
you processed them, you might want to change the order of the recordsets in
the stored proc, or you can use GetRows to put the first recordset into an
array for later use, then access the contents of the second recordset.

--
Mike Brind

Re: getting return from srtored procedure

Mike Brind wrote:
> "noLoveLusT" wrote in message
> news:Qv2fh.86770$bz5.47255@fe3.news.blueyonder.co.uk...
>> hi everyone i am very very new to the sql server (2 days actually
>> and ) so far i learned creating SPs etc but couldnt workout how to
>> get return value from my prodecure
>>
>> my sp as follows
>> ________________________
>> CREATE PROCEDURE [dbo].[page_all_artists]
>> @PageIndex INT,
>> @PageSize INT,
>> @Total INT OUTPUT
>> AS
>> BEGIN
>> WITH Entries AS (
>> SELECT ROW_NUMBER() OVER (ORDER BY ArtistID asc)
>> AS Row, Artist, SongCount
>> FROM artists_table)
>> SELECT Row, Artist, SongCount
>> FROM Entries
>> WHERE Row between
>> (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
>>
>>
>> -----------------THIS ONE TO GET TOTAL RECORD COUNT AND I HAVE NO
>> IDE HOW IT SHOULD WORK---------------------
>>
>> Select @total=Count(ArtistID) from (Select ArtistID From Entries e
>> group by e.ArtistID) b;
>>
>> END

>
> Your procedure creates two recordsets that are returned at the same
> time. The contents of the second one can be accessed through the
> NextRecordSet method:

Huh? I only see one resultset, unless you are talking about the
informational message returned as a closed recordset because of the lack of
"set nocount on"..
He's using an output parameter to return the total records.



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: getting return from srtored procedure

noLoveLusT wrote:
> hi everyone i am very very new to the sql server (2 days actually and
> ) so far i learned creating SPs etc but couldnt workout how to get
> return value from my prodecure
>
> my sp as follows
> ________________________
> CREATE PROCEDURE [dbo].[page_all_artists]
> @PageIndex INT,
> @PageSize INT,
> @Total INT OUTPUT
> AS
> BEGIN

The first line here should be:
SET NOCOUNT ON

to prevent the spurious "x rows effected" messages from being returned to
the client as closed recordsets

> WITH Entries AS (
> SELECT ROW_NUMBER() OVER (ORDER BY ArtistID asc)
> AS Row, Artist, SongCount
> FROM artists_table)
> SELECT Row, Artist, SongCount
> FROM Entries
> WHERE Row between
> (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
>
>
> -----------------THIS ONE TO GET TOTAL RECORD COUNT AND I HAVE NO
> IDE HOW IT SHOULD WORK---------------------
>
> Select @total=Count(ArtistID) from (Select ArtistID From Entries e
> group by e.ArtistID) b;

I've never used CTEs ... is Entries still available at this point? Does this
procedure do what you want in Query Analyzer? Test it like this:

DECLARE @total int
EXEC page_all_artists '20060101',25, @total output
select @total as Total



You need to use an explicit Command object, appending Parameter objects to
its Parameters collection in order to retrieve the value of the output
parameter. Coding these Parameter objects is tedious and error-prone so I
created a utility page to do it for me. You can get it from:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip

Using your procedure's declaration, this is the output of the code
generator:

Dim cmd, param

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "page_all_artists"
set .ActiveConnection=cnSQL
set param = .createparameter("@RETURN_VALUE", adInteger, _
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@PageIndex", adInteger, _
adParamInput, 0, [put value here])
.parameters.append param
set param = .createparameter("@PageSize", adInteger, _
adParamInput, 0, [put value here])
.parameters.append param
set param = .createparameter("@Total", adInteger, _
adParamInputOutput, 0, [put value here])
.parameters.append param
.execute ,,adexecutenorecords
end with

This assumes you have the ADO constants defined either for the page or the
application - see http://www.aspfaq.com/show.asp?id=2112

You would modify it as follows:

Dim cmd, param

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "page_all_artists"
set .ActiveConnection=objConn
set param = .createparameter("@RETURN_VALUE", adInteger, _
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@PageIndex", adInteger, _
adParamInput, 0, startFrom )
.parameters.append param
set param = .createparameter("@PageSize", adInteger, _
adParamInput, 0, perPage)
.parameters.append param
set param = .createparameter("@Total", adInteger, _
adParamOutput)
.parameters.append param
set objRS = .execute
'the procedure returns records so don't specify no-records
end with

The first step is to process and close the recordset so the output parameter
value will be retrieved. I typically do this by using a GetRows array:

dim arData
if not objRS.eof then arData = objRS.GetRows
objRS.close: set objRS = nothing

Then get the output parameter value - since that was the last parameter
defined, param still refers to it so:
dim total: total = param.value

If it wasn't the last parameter, then get it explicitly by:
dim total: total = cmd.parameters("@Total").value



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: getting return from srtored procedure

Oh my god ! i fee so dumb :)

i couldnt get it working :S. all i want is to get a return rom my
stored procedure so i can calculate my paging

Select @total=Count(ArtistID) from (Select ArtistID From Entries e
group by e.ArtistID) b;

i found this on the net and trying to get it working but i also noticed
that i can get it as pard of record set see :
http://www.4guysfromrolla.com/webtech/062899-1.shtml
there

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRe


loogs like what i am looking for
so i have used it as follows and it didnt work either. there was
noreturn as objRs("MoreRecords")

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER PROCEDURE [dbo].[page_all_artists]
@PageIndex INT,
@PageSize INT
AS

BEGIN

WITH Entries AS (
SELECT ROW_NUMBER() OVER (ORDER BY ArtistID asc)
AS Row, ArtistID,Artist, SongCount
FROM artists_table)

SELECT Row, ArtistID,Artist, SongCount,

MoreRecords = (
SELECT COUNT(*)
FROM entries TI
WHERE TI.ArtistID >= @PageIndex*@PageSize
)



FROM Entries
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END

Re: getting return from srtored procedure

Thanks to everyone i have managed to get row count as follows, i can get
total from server as recordset and move to next record set

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[search_with_like]

@PageIndex INT,

@PageSize INT,

@q varchar(100),

@Total INT OUTPUT

AS

SET NOCOUNT ON

BEGIN

SELECT Count(LyricID) FROM lyrics_table where SongName LIKE '%' +@q + '%'

SET @Total = @@ROWCOUNT;





WITH Entries AS (

SELECT ROW_NUMBER() OVER (ORDER BY LyricID asc)

AS Row,LyricID, Artist, SongName

FROM lyrics_table where SongName LIKE '%' +@q + '%')

SELECT Row, LyricID,Artist, SongName

FROM Entries

WHERE Row between

(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize



END



















wrote in message
news:1165844929.440981.68840@f1g2000cwa.googlegroups.com...
> Oh my god ! i fee so dumb :)
>
> i couldnt get it working :S. all i want is to get a return rom my
> stored procedure so i can calculate my paging
>
> Select @total=Count(ArtistID) from (Select ArtistID From Entries e
> group by e.ArtistID) b;
>
> i found this on the net and trying to get it working but i also noticed
> that i can get it as pard of record set see :
> http://www.4guysfromrolla.com/webtech/062899-1.shtml
> there
>
> -- Now, return the set of paged records, plus, an indiciation of we
> -- have more records or not!
> SELECT *,
> MoreRecords =
> (
> SELECT COUNT(*)
> FROM #TempItems TI
> WHERE TI.ID >= @LastRec
> )
> FROM #TempItems
> WHERE ID > @FirstRec AND ID < @LastRe
>
>
> loogs like what i am looking for
> so i have used it as follows and it didnt work either. there was
> noreturn as objRs("MoreRecords")
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
>
>
>
> ALTER PROCEDURE [dbo].[page_all_artists]
> @PageIndex INT,
> @PageSize INT
> AS
>
> BEGIN
>
> WITH Entries AS (
> SELECT ROW_NUMBER() OVER (ORDER BY ArtistID asc)
> AS Row, ArtistID,Artist, SongCount
> FROM artists_table)
>
> SELECT Row, ArtistID,Artist, SongCount,
>
> MoreRecords = (
> SELECT COUNT(*)
> FROM entries TI
> WHERE TI.ArtistID >= @PageIndex*@PageSize
> )
>
>
>
> FROM Entries
> WHERE Row between
> (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
>
> END
>