SQL Server stored procedures - update column based on variable name..?

I have a data driven site with many stored procedures. What I want to eventually be able to do is to say something like:

For Each @variable in sproc inputs
    UPDATE @TableName SET @variable.toString = @variable
Next

I would like it to be able to accept any number of arguments.

It will basically loop through all of the inputs and update the column with the name of the variable with the value of the variable - for example column "Name" would be updated with the value of @Name. I would like to basically have one stored procedure for updating and one for creating. However to do this I will need to be able to convert the actual name of a variable, not the value, to a string.

Question 1: Is it possible to do this in T-SQL, and if so how?

Question 2: Are there any major drawbacks to using something like this (like performance or CPU usage)?

I know if a value is not valid then it will only prevent the update involving that variable and any subsequent ones, but all the data is validated in the vb.net code anyway so will always be valid on submitting to the database, and I will ensure that only variables where the column exists are able to be submitted.

Many thanks in advance,

Regards,

Richard Clarke

Edit:

I know about using SQL strings and the risk of SQL injection attacks - I studied this a bit in my dissertation a few weeks ago.

Basically the website uses an object oriented architecture. There are many classes - for example Product - which have many "Attributes" (I created my own class called Attribute, which has properties such as DataField, Name and Value where DataField开发者_StackOverflow is used to get or update data, Name is displayed on the administration frontend when creating or updating a Product and the Value, which may be displayed on the customer frontend, is set by the administrator. DataField is the field I will be using in the "UPDATE Blah SET @Field = @Value".

I know this is probably confusing but its really complicated to explain - I have a really good understanding of the entire system in my head but I cant put it into words easily.

Basically the structure is set up such that no user will be able to change the value of DataField or Name, but they can change Value. I think if I were to use dynamic parameterised SQL strings there will therefore be no risk of SQL injection attacks.

I mean basically loop through all the attributes so that it ends up like:

UPDATE Products SET [Name] = '@Name', Description = '@Description', Display = @Display

Then loop through all the attributes again and add the parameter values - this will have the same effect as using stored procedures, right??

I dont mind adding to the page load time since this is mainly going to affect the administration frontend, and will marginly affect the customer frontend.

---

Question 1: you must use dynamic SQL - construct your update statement as a string, and run it with the EXEC command.

Question 2: yes there are - SQL injection attacks, risk of mal-formed queries, added overhead of having to compile a separate SQL statement.

---

Your example is very inefficient, so if I pass in 10 columns you will update the same table 10 times?

The better way is to do one update by using sp_executesql and build this dynamically, take a look at The Curse and Blessings of Dynamic SQL to see how you have to do it

---

Is this a new system where you have the freedom to design as necessary, or are you stuck with an existing DB design?

You might consider representing the attributes not as columns, but as rows in a child table.

In the parent MyObject you'd just have header-level data, things that are common to all objects in the system (maybe just an identifier). In the child table MyObjectAttribute you'd have a primary key of with another column attrValue. This way you can do an UPDATE like so:

UPDATE MyObjectAttribute 
    SET attrValue = @myValue
    WHERE objectID = @myID
    AND attrName = @myAttrName