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

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"~TheIcemanCometh~" <bhazelwood@.delta-elevator.com> wrote in message
news:8d372e43.0402171320.5d263673@.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|||[posted and mailed, please reply in news]

~TheIcemanCometh~ (bhazelwood@.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, sommar@.algonet.se

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

Passing a variable to the UPDATE Command

Hello everybody,

I have a problem in passing a variable into the Update command. This is my stored procedure.

CREATE PROCEDURE UpdateTable @.tsID INT, @.UpdateDesc varchar(1000)
AS

DECLARE @.TableNumber INT
DECLARE @.TableName NVARCHAR(100)
DECLARE @.SQL VARCHAR(100)

/* I have to get the name of the table to be updated by first getting the TableId
( an INT field) from a table TS_SUBTASKS and with that the table name from another table TS_TABLES */

SELECT @.TableNumber = TS_SUBTABLEID FROM TS_SUBTASKS
WHERE TS_SUBITEMID = @.tsID

SELECT @.TableName = TS_DBNAME FROM TS_TABLES
WHERE TS_ID = @.TableNumber

/* Which the Table name in the variable @.TableName i have to set one of its
fields, TS_DESCRIPTION to the value passed into the procedure
(@.UpdateDesc) */

SET @.SQL = 'update ' + @.TableName+ 'SET TS_DESCRIPTION = ' + @.UpdateDesc + ' WHERE TS_ID = ' + @.tsID

EXEC(@.SQL)

GO

--But i get this error

Syntax error converting the varchar value 'update USR_HUMAN_RESOURCESSET TS_DESCRIPTION = newdescription WHERE TS_ID = ' to a column of data type int.

It would be very helpful if somebody could throw light on this.
Thanks,
Krishna Murthy.

You should really rethink your approach of using dynamic SQL and this type of design. It requires lot of work in terms of giving permissions to users directly to update the table and coding the dynamic SQL. It is best to use static SQL wherever possible since it is more secure and easier to manage. The errors you are seeing are a result of incorrecly formed UPDATE statement. I believe it is missing some quotes. You should also consider using sp_executesql since that is easier to parameterize like below and you can avoid some of the SQL injection problems with using EXEC incorrectly:

DECLARE @.SQL nvarchar(1000)
SET @.SQL = N'update ' + QUOTENAME(@.TableName) +
N'SET TS_DESCRIPTION = @.Descr WHERE TS_ID = @.ID'
exec sp_executesql @.SQL, N'@.Descr varchar(1000), @.ID int', @.Descr = @.UpdateDesc, @.ID = @.tsID
|||Thank you Mr. Jayachandran, your correction has helped me a great deal. And coming to your suggestion of not taking this approach, I could not find any other way of doing the same. I shall again be very thankful to you if you can suggest a better way of doing this.

Thanks,
Krishna Murthy.|||I meant that you should avoid writing code that relies on dynamic schema elements. They are difficult to manage and code. In your case, you may want to consider generating some of the SP code during the creation at the time of definition of the row that represents a table. This approach also depends on your application and requirements.

Passing a table name

I would like to pass the name of a table in my update query. It goes something like :
set @.Table = 'TableA'
UPDATE
@.Table
SET
Company_id = @.Company_id
However I get a syntax error when I try to pass the table name like this.Try this

Declare
@.Table nvarchar(25)
, @.sql nvarchar(500)
, @.Company_id nvarchar(15)

Set @.Table = 'TableA'
Set @.Company_id = 'MyCompany'

Set @.sql = 'Update ' + @.Table + ' Set Company_id = ''' + @.Company_id + ''''

Print @.sql
--Exec sp_executesql @.sql

This will show you what the statement would look like if you hard coded it.

Then uncomment out this line "Exec sp_executesql @.sql" and the statement will execute.

later,
mkal

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

Pass XML Data to a Stored Procedure

Does anyone know how to pass a XML File to a MS SQL 2005 Stored Procedure (INSERT/UPDATE), and how to create the stored procedure so it will accept the XML values using VB 2005.

Here is an example of the XML File.

Code:
<MYROOT>
<TableName>
<Field1>String</Field1>
<Field2>String</Field2>
<Field3>String</Field3>
</TableName>
</MYROOT>

Thank you.
bty The following link provided by Microsoft does not work in VB 2005, I have tried that

http://support.microsoft.com/default.aspx?scid=kb;en-us;555266

You can do this using XQuery to strip the values and pass them to the stored proc. You can view a good article on using XQuery here:

http://www.15seconds.com/issue/050803.htm

***

Download this free script (WSP Snapshot 1.0) to take snapshot sample of your web server(s) from anywhere/anytime using a browser. View the stats (cpu and disk stress, available memory, requests queued, request wait time and more) on a cellphone or PDA also. http://www.ifusionsoft.com

|||

Also check out Sushil's weblog on this ->

http://blogs.msdn.com/sushilc/archive/2004/08/03/207162.aspx

This explains the basics of sending XML to SQL 2005 as a parameter.