October 1, 2005 at 2:24 pm
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?
October 1, 2005 at 5:08 pm
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
October 1, 2005 at 5:18 pm
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?
October 1, 2005 at 5:27 pm
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
October 1, 2005 at 5:38 pm
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