How to make an insert with a condition | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to make an insert with a condition

I am trying to write a stored procedure that makes an insert into a row according to the primary key returned by the application, but I don#%92 t know how to do it, now I have tried it thus: (but it doesn#%92 t work) USE market1
GO
CREATE PROC Form2_NewUser
@User_id bigint, @User_name varchar(50), @User_surname varchar(50), @City_num int, @Address varchar(100) AS SET NOCOUNT ON INSERT INTO Users (User_name, User_surname, City_num, Address) VALUES (@User_name, @User_surname, @City_num, @Address) WHERE User_id = @User_id SET NOCOUNT OFF
GO The error message says: Incorrect syntax near of ‘WHERE#%92 key word. How can I do it? Thank you,
Cesar

Why do you use a WHERE clause at all ? Frank
http://www.insidesql.de
http://www.familienzirkus.de
I want to do the insert in the table row in which the ‘User_id’ field is equal to the returned value ‘@User_id’. I suppose the ‘where’ clause is not correct as the error message indicates. How can I do it?
IIRc, you should use UPDATE …
–Frank
http://www.insidesql.de

Ok, now it works thus:
USE market1
GO
CREATE PROC Form2_NewUser
@User_id bigint, @User_name varchar(50), @User_surname varchar(50), @City_num int, @Address varchar(100) AS SET NOCOUNT ON UPDATE Users
SET User_name = @User_name, User_surname = @User_surname,
City_num = @City_num, Address = @Address
WHERE User_id = @User_id SET NOCOUNT OFF
GO But, Is it not possible to write the SP separating column names and values? Something like this?:

UPDATE Users
SET (User_name, User_surname, City_num, Address)
VALUES (@User_name, @User_surname, @City_num, @Address) WHERE User_id = @User_id

Thanks
Nopes. I think this is not supported even by SQL Standard. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
]]>