How to insert into relational tables

Question

I am learning SQL Server as a bit of a hobby. I am stuck in inserting data into relational tables, I understand the ideas of relational tables.

I was wondering if anyone could tell me how I can get the identity of the previously inserted row, store it as a variable that will be used as FK in the rest of the queries.

Here is my TSQL code:

CREATE PROCEDURE [dbo].[q_insertuser] @username varchar(50), @hash varchar(MAX), @name varchar(50), @email varchar(MAX), @address varchar(MAX), @city varchar(50), @postcode varchar(50) AS --INSERT USERNAME INSERT INTO tab_user (username) VALUES (@username) --Selects the Userid to be used DECLARE @UID INT SET @UID = INT FOR SELECT * FROM tab_user WHERE (userid = SCOPE_IDENTITY()) --INSERT PASSWORD INSERT INTO tab_pass (userid, hash) VALUES (@UID ,@hash) --INSERT Address INSERT INTO tab_contact (userid,name, email, address, city, postcode) VALUES (@UID ,@name, @email, @address, @city, @postcode) --RETURN 0 

The examples I saw on the Internet seem to do it this way, but I get a typical cryptic error from VS

(76.1): SQL72014: .Net SqlClient data provider: Msg 207, level 16, state 1, q_insertuser procedure, line 19 Invalid column name 'userid'.
An error occurred while executing a batch.

However, the userid column name is the table I'm querying.

Answer 1

replace this part

 DECLARE @UID INT SET @UID = INT FOR SELECT * FROM tab_user WHERE (userid = SCOPE_IDENTITY()) 

with this

 DECLARE @UID INT SET @UID = SCOPE_IDENTITY() 

You can directly assign the SCOPE_IDENTITY () function to a variable, like getdate () or db_name ().