getting return from srtored procedure

Discussion in 'ASP General' started by noLoveLusT, Dec 11, 2006.

  1. noLoveLusT

    noLoveLusT Guest

    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
    noLoveLusT, Dec 11, 2006
    #1
    1. Advertising

  2. noLoveLusT

    Mike Brind Guest

    "noLoveLusT" <> wrote in message
    news:Qv2fh.86770$...
    > 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.

    --
    Mike Brind
    Mike Brind, Dec 11, 2006
    #2
    1. Advertising

  3. Mike Brind wrote:
    > "noLoveLusT" <> wrote in message
    > news:Qv2fh.86770$...
    >> 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

    <snip>
    >
    > 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"
    Bob Barrows [MVP], Dec 11, 2006
    #3
  4. 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

    <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



    --
    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"
    Bob Barrows [MVP], Dec 11, 2006
    #4
  5. noLoveLusT

    Guest

    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
    , Dec 11, 2006
    #5
  6. noLoveLusT

    noLoveLusT Guest

    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:...
    > 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, Dec 11, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike P
    Replies:
    0
    Views:
    3,297
    Mike P
    Jun 19, 2006
  2. Seong-Kook Shin
    Replies:
    1
    Views:
    489
    Richard Bos
    Jun 18, 2004
  3. Greenhorn
    Replies:
    15
    Views:
    812
    Keith Thompson
    Mar 6, 2005
  4. AlexWare
    Replies:
    2
    Views:
    754
    Paul Uiterlinden
    Oct 23, 2009
  5. Vipul Pathak

    Getting Return Value of Stored Procedure

    Vipul Pathak, Jul 30, 2003, in forum: ASP General
    Replies:
    3
    Views:
    254
    Bob Barrows
    Jul 30, 2003
Loading...

Share This Page