getting return from srtored procedure

[noLoveLusT]

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

 

[Mike Brind]

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") & "<br>"
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.

 

[Bob Barrows [MVP]]

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.

 

[Bob Barrows [MVP]]

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

<snip>

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

 

[nolovelust]

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

 

[noLoveLusT]

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