Passing an Array and/or Variable Field Name to an SProc

[~TheIcemanCometh~]

I have 2 questions.

I am trying to write a stored procedure to update a table. I am trying
to pass a variable that represents the name of the column/field and
another for the value that I am changing.

For example:
@FieldName VARCHAR(100)
@FieldValue VARCHAR(100)
AS
UPDATE tblTHETABLE
SET @FieldName = @FieldValue

First is it possible to use a variable as the column/field name? If
so, how do I go about it?

Also, it would be nice if I could have the @FieldName and @FieldValue
variables as arrays. Is that possible?

Thank-you for any assistance
Bill

[Simon Hayes]

"~TheIcemanCometh~" <bh********@delta-elevator.com> wrote in message
news:8d**************************@posting.google.c om...

I have 2 questions.

I am trying to write a stored procedure to update a table. I am trying
to pass a variable that represents the name of the column/field and
another for the value that I am changing.

For example:
@FieldName VARCHAR(100)
@FieldValue VARCHAR(100)
AS
UPDATE tblTHETABLE
SET @FieldName = @FieldValue

First is it possible to use a variable as the column/field name? If
so, how do I go about it?

Also, it would be nice if I could have the @FieldName and @FieldValue
variables as arrays. Is that possible?

Thank-you for any assistance
Bill

The short answer is that it's possible, but probably not advisable. The
first link should help explain why; the second covers arrays:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html

Simon

[Erland Sommarskog]

[posted and mailed, please reply in news]

~TheIcemanCometh~ (bh********@delta-elevator.com) writes:

I am trying to write a stored procedure to update a table. I am trying
to pass a variable that represents the name of the column/field and
another for the value that I am changing.

For example:
@FieldName VARCHAR(100)
@FieldValue VARCHAR(100)
AS
UPDATE tblTHETABLE
SET @FieldName = @FieldValue

First is it possible to use a variable as the column/field name? If
so, how do I go about it?

Also, it would be nice if I could have the @FieldName and @FieldValue
variables as arrays. Is that possible?

Anything is possible, but what's the point? Why not construct the
SQL statements in client code instead?

If you really want to know how to do it, I have an article on my web
site. There you also learn why you should not do it.
http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp