CODEWITHSUNDEEP
.netsql-serverstored-proceduresforeign-keys
DarthVader
DarthVader

Reputation: 55022

Sql server Stored Procedure

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?

Upvotes: 1

Views: 226

Answers (3)

egrunin
egrunin

Reputation: 25053

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

Upvotes: 1

Malevolence
Malevolence

Reputation: 1857

The more succinct syntax is to use something like this:

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

Upvotes: 4

SQLMenace
SQLMenace

Reputation: 134961

This should work for one or more keys

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

Upvotes: 2

Related Questions