simple query

  • this is my stored procedure and it seems to work right but I am having some trouble accessing the results.

    <code>

    AS

    Declare

    @rowcount int

    Select FirstName, LastName, ClientID, UserName

    From dbo.aspnet_Users

    Where ClientID = @user-id

    Select @rowcount = @@rowcount

    If @rowcount = 1

    Return 0

    Else

    Begin

    Select

    First_Name as FirstName, Last_Name as LastName, User_ID as ClientID, User_Name as UserName

    from

    dbo.User_Information

    where User_ID

    = @user-id

    End

    Select

    @rowcount = @@rowcount

    If

    @rowcount = 0

    RETURN -1

    Else return 0

    </code>

    and these are the results if the first select statement finds a value

    FirstName LastName ClientID UserName

    Dave Smith 123 dsmith

    No more results.

    (1 row(s) returned)

    @RETURN_VALUE = 0

    I can access these results when I use a reader on my asp.net page

    and these are the results if the first didn't find a value but the second select statement did

    No rows affected.

    FirstName LastName ClientID UserName

    Eric Grant 2167 egrant

    No more results.

    (1 row(s) returned)

    @RETURN_VALUE = 0

    I can't access these results when I use a reader on my page.

    the only difference in the results is "No rows affected" in the second set of results.

    What am I doing wrong?

  • Your reader is probably confused because of the '...rows affected' message that is returned first. To fix this, add as the first thing in your proc

    <code>

    AS

    SET NOCOUNT ON

    Declare

    @rowcount int

    .....

    /Kenneth

     

  • I have tried setting nocount and it still is not working. I ran the procedure with sql analyzer and it returned to sets of data the first being empty and the second has the reuslts. How do I merge the two datasets?

  • Yes, you have written two select statements, so there will always be two resultsets returned (with or without rows)

    If this is an exclusive case, meaning that you'll always have one row in either table, you can replace the if logic with a union instead..

    SELECT ... FROM table1 WHERE id = @id

    UNION ALL

    SELECT ... FROM table2 WHERE otherID = @id

    Will that work for you?

    /Kenneth

  • Worked perfect. Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

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