manually return more than one piece of data from a

Results 1 to 2 of 2

Thread: manually return more than one piece of data from a

  1. #1
    Join Date
    Dec 1969

    Default manually return more than one piece of data from a

    Ok I have a sproc that goes into the userprofile and get&#039;s the ID of the countrry he is from. Based on the country, if he is in the US or CAN it returns the states or provinces respectively from another table. If the user if not from US or CAN then it simply returns &#039;0&#039;, from ASP <BR>If ORs(0) &#060;&#062; 0 Then print the states or prov else<BR>print nothing assuming intl user. Now I want to return more than simply the states or 0. Say I also want to return the name of the country which is stored in yet another table. So this is what the tables look like<BR>------------<BR>UserInfo<BR>------------<BR>Username (pk)<BR>CountryID<BR>....<BR><BR>------------<BR>CountryNames<BR>------------<BR>CountryID (pk)<BR>CountryName<BR><BR><BR>------------<BR>StatesProv (both CAN and US are in here with different CountryID)<BR>------------<BR>StateID (both prov and states in here) (Pk)<BR>StateName (same)<BR>CountryID (fk)<BR><BR>SO my question is instead of just returning one piece of data I want to to return more than one. For exaple say I want to return the country Name so I can display it as well as the states or prov if the user is not intl, all with one db call from my spoc. How Do I return more than one piece of data that way?<BR><BR>I am currently using a select statement. Must I make a join statement to do this?<BR><BR>Thanks<BR>Mike M

  2. #2
    Join Date
    Dec 1969

    Default RE: manually return more than one piece of data fr

    SELECT @Country = Users.CountryID FROM Users WHERE Users.UserID = @UserID<BR>SELECT Country = @Country<BR><BR>IF @Country = &#039;US&#039; OR @Country = &#039;CAN&#039; <BR>BEGIN<BR>SELECT States.StateName FROM States WHERE States.CountryID = @Country<BR>END<BR>In the ASP page you use rsRecordset.NextRecordset to get the values of the 2nd row of data, so:<BR><BR>rsRecordset(0) gives you &#039;US&#039;<BR><BR>Then do: rs.nextrecordset<BR><BR>rsRecordset(0) gives you Alabama<BR>rsRecordset.movenext<BR>rsRecordset(0) gives you Florida etc. etc<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts