Devin Gray Devin Gray - 5 months ago 8
SQL Question

SQL stored procedure to get a user object and then update the values of that object

I am looking to make a single SQL statement to select an object from the database and then update that object based on values entered through a form.

I am not used to writing advanced SQL queries and am used to ORM syntaxes, but I would like to get this right.

Currently my query looks like this

DECLARE @person;
SET @person = (
select u.*
from
dbo.people as u
inner join dbo.people as pe on pe.people_id = u.people_id
inner join dbo.parties as pa on pa.people_id = pe.people_id
inner join dbo.contact_details as cd on cd.parties_id = pa.parties_id
inner join enum.contact_details_types cdt on cdt.contact_details_types_id = cd.contact_details_types_id
where
u.people_id = 57121526-03e3-4dc2-bfb8-6c6fc84a9de8 #UUID of the user eg)


UPDATE @person
SET
sexes_id=test
marital_states_id=test
employment_states_id=test
linkedins_id=test
facebooks_id=test
first_names=test
surnames=test
name_to_call_user=test
id_number=test
passport_number=test
birth_date=test
tax_number=test
vat_number=test
message_to_show_on_user_invoice=test
workplace_name=test


I have been reading up about stored proceedures and know that I need to create something more than just a simple insert into query.

ASK:

My question is, how do I create a query that will select this user as an object allowing the values in the database to be altered with an update statement?

Error i am getting:

My current error is that I cannot declare person in this way.
Any help would be greatly appreciated

Answer

You may need this, try it.

update dbo.people u
inner join dbo.people as pe on pe.people_id = u.people_id
inner join dbo.parties as pa on pa.people_id = pe.people_id
inner join dbo.contact_details as cd on cd.parties_id = pa.parties_id
inner join `enum`.contact_details_types cdt on cdt.contact_details_types_id = cd.contact_details_types_id
on u.people_id = 57121526-03e3-4dc2-bfb8-6c6fc84a9de8'
set
    u.sexes_id = ?,
    u.marital_states_id = ?,
    u.employment_states_id = ?,
    u.linkedins_id = ?,
    u.facebooks_id = ?,
    u.first_names = ?,
    u.surnames = ?,
    u.name_to_call_user = ?,
    u.id_number = ?,
    u.passport_number = ?,
    u.birth_date = ?,
    u.tax_number = ?,
    u.vat_number = ?,
    u.message_to_show_on_user_invoice = ?,
    u.workplace_name = ?;