Link to home
Start Free TrialLog in
Avatar of visionetv
visionetvFlag for United States of America

asked on

SQL Dynamic Query with Column Variable

Hello Experts;

I am trying to UPDATE a SQL table [SRSPurchase] where a column name is a variable. The UPDATE result is based on matching a variable "@OrderNo" with the same value in a Column named "edOrderNo"

Here is the query:

Declare @ColumnName AS varchar(10) =:ColumnName
Declare @ParValue AS varchar(10) =:ParValue
Declare @OrderNo AS varchar(10) =:OrderNo
Declare @Sql nvarchar(max)


SELECT @Sql = '
UPDATE
[SRSPurchase]
SET '+@ColumnName+ ' = '+ @ParValue+ ''
WHERE
edOrderNo = @OrderNo

EXEC (@Sql)

Open in new window


When the query is executed I get an error 'edOrderNo is an Invalid column name'

Please help me in writing the query correctly

Thank you,
Visionetv
Avatar of Pratima
Pratima
Flag of India image

first of all check what are you getting in @Sql as string
Run that result on SQL server query analyzer directly

I think there is some issue in creating string

check if 'edOrderNo is correct column name
You didn't close the single quote:
SELECT @Sql = '
	UPDATE
	[SRSPurchase]
	SET '+@ColumnName+ ' = '+ @ParValue+ '
	WHERE
	edOrderNo = @OrderNo'

Open in new window

Avatar of visionetv

ASKER

Vitor;

Closing the dynamic SQL statement quote to include the WHERE clause as you suggest results in 'Query OK' but no rows affected or values written to the SRSPurchase table. Seems like the WHERE clause is ignoring the value of the @OrderNo variable. Does it need to be Declared somewhere else in the Query?

Also, if I remove the WHERE clause the Query writes the value in the @ParValue variable to the @ColumnName  column for all rows in the SRSPurchase table.
ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jogos;

Thank you for your solution. By removing one of the quotation marks from your re-structuring of the @OrderNo variable the Query now works properly.

Also, while SQL injection is always a security concern in this instance the Query is used on two workstations on a small secure LAN for preparing Purchase Orders. Varchar is used because the users append notes to the par value entries for internal use which are parsed and converted to an integer when required.

Thanks again,
Visionetv