multiple select statements

Hi guys and gals,

I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:

'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

My Code is below:

//Start of sql

CREATE PROCEDURE ADMIN_GetSingleUsers
(
@.userID int
)
AS

DECLARE @.userSQL int
SET @.userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
Users.Unit_ID As usersUnitID,
*
From TITLE, Users
WHERE
User_ID = @.userID AND
TITLE.TITLE_ID = Users.Title_ID )

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

//End of sql

Can you point to what I am doing wrong? Thanks in advance!

You are trying to SET @.userSQL to more than one value (User_ID, Title.Title, Users.Active, Users.Approved, and Users.Unit_ID).

Try it in one statement instead, something like this:

SELECT
Units.Unit_ID,
Units.Parent_ID,
Units.Unit_Name
FROM
Units
INNER JOIN
Users ON Units.Unit_ID = Users.Unit_ID AND Users.User_ID = @.UserID
INNER JOIN
Title ON Users.Title_ID = Title.Title_ID

|||

Depends on if you wanted both result sets to be returned or just the second one.

return both:

SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
@.userSQL=Users.Unit_ID As usersUnitID,
*
From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

return second one:

SELECT @.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

Or if you don't need @.userSQL except for limiting the second query:

SELECT Unit_ID, Parent_ID, Unit_Name@.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

JOIN UNITS ON (Users.Unit_ID=UNITS.Unit_ID)

WHERE User_ID = @.userID

|||

Another Question.

Is there a way to do the following?

DECLARE unitID nVarChar(255)
SET @.unitID = (Select * From Units)

and give @.unitID the exact value from *?
Or loop throug the @.unitID and get the value I need?

|||Declare it as a table?