Post reply

Returning value along with a table

  • September 14, 2009 at 3:16 am

    #135638

    I am writing a procedure for selecting a table, I want to get the count of the rows along with the table.

    My proc:

    ALTER PROC USP_SELECT_PRODUCTS_BY_PAGESIZE

    (

    @STARTROWINDEX INT,

    @PAGESIZE INT,

    @TOTALCOUNT INT OUTPUT

    )

    AS

    SET @STARTROWINDEX = @STARTROWINDEX + 1;

    WITH PRODUCTLIST AS (

    SELECT *, ROW_NUMBER() OVER (ORDER BY ProdId ASC) as ROWNUM FROM [TblProduct]

    )

    SELECT * FROM PRODUCTLIST WHERE ROWNUM BETWEEN @STARTROWINDEX AND (@STARTROWINDEX + @PAGESIZE) - 1

    ORDER BY PRODID ASC

    SET @TOTALCOUNT = (SELECT COUNT(PRODID) FROM [TblProduct])

    Will I get the Output value apart from the table?

  • September 14, 2009 at 3:22 am

    #1053024

    @@Rowcount should be fine...


    Clear Sky SQL
    My Blog[/url]

  • September 14, 2009 at 3:27 am

    #1053026

    Dave Ballantyne (9/14/2009)

    @@Rowcount should be fine...

    No no I need total rowcount of Prodtable, not the selected one. But it's value is showing NULL from front end.

  • September 14, 2009 at 3:31 am

    #1053030

    Are you calling the stored proc correctly ?, you need to specify OUTPUT at the caller level if you expect a value.


    Clear Sky SQL
    My Blog[/url]

  • September 14, 2009 at 3:42 am

    #1053037

    yes everything is ok from front end, does OUTPUT parameter works with Select statement?

    my front end code:

    SqlParameter param = new SqlParameter("@TOTALCOUNT", DbType.Int32);

    param.Direction = ParameterDirection.Output;

    cmd.Parameters.Add(param);

    con.Open();

    SqlDataReader dr = cmd.ExecuteReader();

    iTotCount = int.Parse(cmd.Parameters[param.ParameterName].Value.ToString());

    parameter value is showing NULL

  • September 14, 2009 at 3:52 am

    #1053041

    -=oops i did not see you were using an output parameters...thought you wanted the RETURN; i'm testing the other way now, and will report back, sorry...jumped to conclusions=-

    oops old advice

    you need to save the count to a declared INT, and then use the RETURN statement to get the value;

    this prototype works; for me i thought I had to get the count first, but when i reversed the order to emulate what you had done, it works fine...i get data and the count:

    alter procedure testproc as

    SET NOCOUNT ON

    declare @count int

    select name,id from sysobjects

    select @count = count(*) from sysobjects

    return @count

    GO

    declare @results int

    exec @results= testproc

    print @results

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 14, 2009 at 5:38 am

    #1053103

    thanx @Lowell problem is solved....

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply