Sql server Stored Procedure

Question

I m passing a variable to stored procedure, i want the proc to make a look up to another table and get the primary key of the table and insert that value to the table.

Table A:
pk_id int,
user varchar

Table B:
userKey int
locationKey int    
Someotherthings varchar

Table C:
pk_id int,
Location varchar

When i invoke sp_howto, and pass user to it, i want to it to get the pk_id from table A and insert it to table B userKey.

set @res = select pk_id from TableA where user=@user
set @loc = select pk_id from TableC where location = @Location
insert into tableB (userKey, locationKey) values (@res, @loc)

Does this work? and what if I have many variables I want to populate like this?

Answer 1

This should work for one or more keys

insert into tableB (userKey) 
select  userKey from TableA where user=@user

Answer 2

The more succinct syntax is to use something like this:

INSERT INTO TableB (UserKey)
SELECT UserKey FROM TableA WHERE User = @User

Answer 3

The question has mutated some--the currently posted code works fine, these are alternatives.

The one-statement way, where you pass two parameters, @User and @Location:

INSERT INTO TableB (UserKey, LocationKey)
SELECT (userKey, LocationKey) 
FROM
(
    (select pk_id as userKey, user from TableA) x
    cross join 
    (select pk_id as LocationKey, Location from TableC) y
    WHERE user = @user and location = @location
)

If if User is unique in TableA, and Location is unique in TableC, this will insert one record into TableB.

Edited to add:

The above will do it in a single statement, but the added complexity gains you nothing. Better to do it like this:

Add a unique key to TableB:

pk_id int IDENTITY

Then do your insert like this:

declare @newID int

INSERT INTO TableB (UserKey)
SELECT pk_id from TableA where user = @user

SELECT @newID = SCOPE_IDENTITY() -- retrieves the new key value

UPDATE TableB set LocationKey = 
    (SELECT pk_id from TableC WHERE location = @location)
WHERE pk_id = @newID