Oracle765 (8/5/2013)
is there a way around this the as the variables@columnname1 and @columnname2 could be named anything when they are passed in as this is selected from a dynamically imported spreadsheet
You could use dynamic SQL, but I wouldn't recommend it if your strings are derived from a "dynamically imported" spreadsheet. What's wrong with extending the logic that you currently have in your stored procedure? This would work:
ALTER procedure [dbo].[updatethecolumn]
@columnname1 varchar(1000),
@columnname2 varchar(1000)
as
begin
--Update the Software Manufacturer and the Product name
if RTRIM(@columnname1) = 'softwaremanufacturer' and RTRIM(@columnname2) = 'productname'
begin
UPDATE dbsource SET
softwaremanufacturer = dbref.Amended_SW_Manufacturer,
productname = dbref.Amended_Product_Name
FROM dbo.newtable dbsource
INNER JOIN (
SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version,
Raw_Product_Name,Amended_Product_Name
FROM datalookuptable
GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version,
Raw_Product_Name,Amended_Product_Name
) dbref
ON dbref.Raw_SW_Manufacturer = dbsource.softwaremanufacturer
--and dbref.Raw_Product_Version = dbsource.productversion
and dbref.Raw_Product_Name = dbsource.productname
end
Another possibility is to list all of the columns which could be updated in the UPDATE, and only update those which appear as parameters - otherwise, update to same (SET softwaremanufacturer = softwaremanufacturer).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden