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

sql, sql-server, sql-server-2008, t-sql

Solution

I think this accomplishes what you're trying to do, except that I've removed the join and just declared a new variable inside the procedure, which grabs the ID of the store based on the name that the user inputs. It then updates the table with the ID of the store, even though the user didn't input the store ID (because the user wouldn't know this, but the system would).

Granted, depending on what you're trying to do, this still may 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 initially, I copied and pasted from my database to test it, then copied it back trying to use the other names (bad idea).

Problem

Consider this little script for 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 stored update called `spUpdateCustomerInformation` that takes two parameters: one is the `CustomerId` and the other is the name of the store which the user wishes to update (StoreName from the Stores table). So in the `Customers` table the record `(1,1)` means that customer 1 bought something from `Biggest Store`. I would like to pass two parameters to the stored procedure like `spUpdateCustomerInformation 1,'Smallest Store'` and after the stored procedure is ran the record that was `(1,1)` is 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 updated. I know that I could simply pass the StoreId as the second parameter to the stored procedure, but I was wondering if it's smart/possible to do it this way, passing the `StoreName` as the second parameter. Pretend this isn't a contrived scenario and `CustomerId` has to be passed as a parameter as well.

Original source