sql - Stored Procedure update with parameter a column name from a different table -

consider little script example

create table customers (customerid int not null ,storeid int ,primary key(customerid) ) go create table stores (storeid int ,storename varchar(50) ,primary key(storeid) ) go alter table customers add foreign key (storeid) references stores(storeid) go  insert stores values   (1,'biggest store')         ,(2,'mediumest store')         ,(3,'smaller store')         ,(4,'smallest store') go insert customers values          (1,1)         ,(2,1)         ,(3,2)         ,(4,3)         ,(5,4) 

let's want use stored update called spupdatecustomerinformation takes 2 parameters: 1 customerid , other name of store user wishes update (storename stores table). in customers table record (1,1) means customer 1 bought biggest store. pass 2 parameters stored procedure spupdatecustomerinformation 1,'smallest store' , after stored procedure ran record (1,1) (1,4). attempt

create proc spupdatecustomerinformation  @customerid int ,@storename varchar(50) begin update c set c.storeid = s.storeid customers c inner join stores s on s.storeid = c.storeid c.customerid = @customerid       , s.storename = @storename --failing here end 

returns 0 rows updated. know pass storeid second parameter stored procedure, wondering if it's smart/possible way, passing storename second parameter. pretend isn't contrived scenario , customerid has passed parameter well.

i think accomplishes you're trying do, except i've removed join , declared new variable inside procedure, grabs id of store based on name user inputs. updates table id of store, though user didn't input store id (because user wouldn't know this, system would).

granted, depending on you're trying do, still may not work.

create procedure spupdatecustomerinformation  @customerid int ,@storename varchar(50) begin  declare @id int select @id = storeid stores storename = @storename   update customers set storeid = @id customerid = @customerid  end 

updated because initially, copied , pasted database test it, copied trying use other names (bad idea).