Using Variable in a Select or Update Statement

[Jimmy211]

Hi,

This will probably seem like a newbie question to the pros around here, but its stumping me. How do you use a variable in place of a field or table name in Select and Update statements?
In my example I want to create a Stored Procedure and pass a field name to it and have it run an update statement.

EXAMPLE:
Create Proc Updating1
@Fieldname as varchar(30)

AS
BEGIN

UPDATE myTable1 SET @Fieldname=10


Do I have to enclose @Fieldname in brackets or something?
Thanks in advance for any help.

 

[bborissov]

No, you must use so called Dynamic SQL:

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] Updating1
  @Fieldname [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](30)

[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]
[COLOR=blue]DECLARE[/color] @lcSQL [COLOR=blue]varchar[/color](2000)
[COLOR=blue]SET[/color] @lcSQL = [COLOR=red]'UPDATE myTable1 SET '[/color]+@Fieldname+[COLOR=red]'=10'[/color]
[COLOR=blue]EXEC[/color] (@lcSQL)
...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP