store Procedure

[marjani]

Hi,
how can i use 2 Query in store Procedure?
I have user table that have username, userId (primary key) field and have userinfo table that have userId ,userinfo(primary key),firstName field

I want to do this:

1.getting userId from user table : (select userId from user where username=@username )
2.adding userId into userinfo table:insert into userinfo ( userId,firstName) values(?????,@firstName)

I don't know,how can i getting value from one table and using it to another table.

Please Help Me.

Thanks.

[Jim Doherty]

@marjani
I am not convinced at all by your logic in retrieving a userid from a table based on some users name and inserting the userid into a second table in the manner you prescribe. Peoples names are not unique (many Jims and Johns etc etc) but I suspect you are learning so you will realise soon enough I suspect.

Anyway, below is a very simple procedure that first SELECTS records existing in the user table (tbluser) matching the value of the username passed in as a parameter to the stored procedure.

Only if the row count of the recordset is equal to one does it insert the values into the second table

The logic of this procedural flow is deeply 'flawed' of course but you can work it out for yourself as I suspect you are merely learning 'how' to grab a value from one table and throw it into another

CREATE PROCEDURE dbo.usp_insertUserInfo
@username varchar(100) AS
 
DECLARE @mycount int
 
SELECT @mycount= (userid)  FROM tblusers WHERE username=@username
IF @@rowcount=1 
INSERT tbluserinfo (userid,firstname)
SELECT userid,username FROM tblusers WHERE username=@username
GO