stored procedures - SQL: Writing a bulk insert script that ignores existing entries -

i'm starting existing program uses sql database , trying modify uses bulk insert (rather one-by-one) , prevents repeat entries.

here's i've got function add 1 user (as long user doesn't exist), should working:

use [debugdatabase] go  set ansi_nulls on go  set quoted_identifier on go  create procedure [dbo].[adduser]     @id bigint, @login nvarchar(100), @consolename nvarchar(100), @ip nvarchar(50) begin     set nocount on;      declare @foundid bigint = (select id [user] id = @id)      if @foundid null         begin             insert [user] (id, login, consolename, ip) values (@id, @login, @consolename, @ip)         end     else         begin             update [user]             set [id] = @id,                 [login] = @login,                 [consolename] = @consolename,                 [ip] = @ip             id = @id         end     select top 1 * [user] id = @id; end go 

now i'd write function bulk-insertion, calls above function check each entry. way shown, requires custom table type:

use [debugdatabase] go  create type [dbo].[usertabletype] table     (     [id] [bigint] not null,     [login] [nvarchar](100) not null default 'unknown',     [consolename] [nvarchar](100) not null default 'unknown',     [ip] [nvarchar](50) not null default '0.0.0.0'     ) go 

and function add multiple entries (this 1 i'm having trouble with):

use [debugdatabase] go  set ansi_nulls on go  set quoted_identifier on go  create procedure [addmultipleusers]     @users usertabletype readonly   declare     @id bigint,     @login nvarchar(100),     @consolename nvarchar(100),     @ip nvarchar(50)  begin     insert @id select id @users     insert @login select login @users     insert @consolename select consolename @users     insert @ip select ip @users      exec adduser @id, @login, @consolename, @ip end go 

i'm getting "must declare table variable "@id". i'm not sure how extract individual values table type in order pass them on adduser function. i'd love, though, way in 1 function call, haven't come across yet.

this type of operation can done using single query without use of stored procedure. (btw, please use correct terminology: adduser stored procedure, not function). please have @ merge command

merge [user] target     using (select [id], [login], [consolename], [ip] @users) source     (id, login, consolename, ip)     on (target.id = source.id) when matched          update set [login] = source.login,                    [consolename] = source.consolename,                    [ip] = source.ip when not matched     insert (id, login, consolename, ip)     values (source.id, source.login, source.consolename, source.ip);