Updating a stored procedure with the column name parameter from another table

Question

Consider this little script as an 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 into Stores values (1,'Biggest Store') ,(2,'Mediumest Store') ,(3,'Smaller Store') ,(4,'Smallest Store') go insert into Customers values (1,1) ,(2,1) ,(3,2) ,(4,3) ,(5,4) 

Let's say that I want to use a saved update called spUpdateCustomerInformation which takes two parameters: one is the CustomerId and the other is the name of the store that the user wants to update (StoreName from the Stores table). Therefore, in the Customers table, the entry (1,1) means that customer 1 bought something from the Biggest Store . I would like to pass two parameters to a stored procedure, for example spUpdateCustomerInformation 1,'Smallest Store' , and after starting the stored procedure, the record that was (1,1) , now (1,4) . My attempt

 create proc spUpdateCustomerInformation @CustomerId int ,@StoreName varchar(50) as begin update c set c.StoreId = s.StoreId from customers as c inner join Stores as s on s.StoreId = c.StoreId where c.CustomerID = @CustomerId and s.StoreName = @StoreName --failing here end 

returns 0 rows. I know that I can simply pass StoreId as the second parameter of the stored procedure, but I was wondering if it is possible to do this / to do so by passing StoreName as the second parameter. Pretend this is not a flimsy scenario, and CustomerId should also be passed as a parameter.

Answer 1

I think this does what you are trying to do, except that I removed the connection and simply declared a new variable inside the procedure that captures the storage identifier based on the name the user enters. Then it updates the table with the store identifier, even if the user did not enter the store identifier (since the user did not know about it, but the system would be).

Of course, depending on what you are trying to do, this may still not work.

 CREATE PROCEDURE spUpdateCustomerInformation @CustomerID int ,@StoreName varchar(50) AS BEGIN DECLARE @ID INT SELECT @ID = StoreId FROM Stores WHERE StoreName = @StoreName UPDATE Customers SET StoreId = @ID WHERE CustomerId = @CustomerID END 

Updated because I originally copied and pasted from my database to check it out and then copied it, trying to use other names (bad idea).

Answer 2

Since you are joining Customers with Stores ON storeID, c.storeID will always be s.storeID ,

In order to accomplish what you are trying to do, there must be a third internal connection to find out what storage @StoreName is @StoreName .