Nested Stored Procedure

Hi, I have two tables:
TABLE 1 Table 2
========== ==========
UID TeamName
TeamID <------> TeamID

and I am trying to obtain the value of TeamName via a nested stored procedure.
However, I am only allowed to obtain the @.UID as input parameter.
I tried writing the following:
Select table2.TeamName
From Table1, Table2
Where (table1.TeamID=table2.TeamID)and (UID = @. UID)
It seems to not work as TeamID was not given.
Thus, my other idea of doing this is to:
1) Execute a sql to first obtain the TeamID value with the @.UID parameter given.
then 2)Execute another sql to obtain the TeamName with the result from the first sql as the input parameter.

However, I have never worked with nested sql, so it would be great if someone can link me a site that they know. Better yet, write me an example code.
Thanks you very much!
You do not need to first obtain the TeamID value in the manner you areproposing. What you have should work OK. I'd rewrite it assuch, but it should function in the same manner:

Select Table2.TeamName
From Table1
Inner Join Table2 ON Table1.TeamID = Table2.TeamID
Where Table1.UID = @. UID

Let us know if you are still experiencing problems.|||let me post the code and see if you can shine some light in it:

Function GetSchoolName(ByVal UIDAsInteger)AsString

Dim mycmdAsNew SqlCommand("GetSchoolName", myconn)

mycmd.CommandType = CommandType.StoredProcedure

mycmd.Parameters.Add("@.UID", SqlDbType.Char, 3)

mycmd.Parameters("@.UID").Value = UID

myconn.Open()

Dim SchoolNameAsString = mycmd.ExecuteScalar

myconn.Close()

Return SchoolName

EndFunction
=============================

ALTER PROCEDUREdbo.GetSchoolName

(

@.UIDchar

)

AS

SELECTCOLLEGE_DIV1A.schoolNameASSchoolName

FROMPOWER_RANKINGS, COLLEGE_DIV1A

WHERE(POWER_RANKINGS.UID = @.UID)and(POWER_RANKINGS.TeamCode = COLLEGE_DIV1A.teamCode)

RETURN

================================

Dim dtUpdateTableAsNew DataTable

dtUpdateTable.Clear()

Dim DcAs DataColumn

Dim intTypeAs System.Type = System.Type.GetType("System.Int32")

'create column

Dc =New DataColumn("UID", intType)

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("Points",GetType(Decimal))

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("Week", intType)

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("SchoolName",GetType(String))

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("OldPoints",GetType(Decimal))

dtUpdateTable.Columns.Add(Dc)

dtUpdateTable.Rows.Add(dtUpdateTable.NewRow())

With dtUpdateTable.Rows(dtUpdateTable.Rows.Count - 1)

.Item("UID") = drPowerRankings.UID

.Item("Points") = drPowerRankings.Points

.Item("OldPoints") = Session("OLD_Points")

.Item("Week") = Session("WeekId")

.Item("SchoolName") = GetSchoolName(drPowerRankings.UID)

EndWith
============================
Problem: The value I got returned from the function, GetSchoolName, is always the first row's first value of the table. Regardless of the UID inserted.
I ran the sql with query analyzer, and the Stored Procedure works fine. But the actual coding doesn't work for some reason. kinda weird...
Thanks again for looking into my code.

|||couple of things.
(1)

mycmd.Parameters("@.UID").Value = UID

should be
mycmd.Parameters("@.UID").Value = "UID" --The value should be inquotes if it is a string.
(2) In your stored proc I'd recommend setting the SIZE for the parameter.

@.UIDchar

would be

@.UIDchar(3)

Other recommendations:
(1) Use SET NOCOUNT option. check out Books on line for more info.
(2) Use OUTPUT Parameter. Since you are only returning one parameter that would be faster with ExecuteScalar. SELECT statement returns a recordset versus OUTPUT parameter returning a single record. Prbly not very noticeable but would be more efficient. check out books on line about using OUTPUT parameter.

sql