visionetv
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:
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
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)
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
You didn't close the single quote:
SELECT @Sql = '
UPDATE
[SRSPurchase]
SET '+@ColumnName+ ' = '+ @ParValue+ '
WHERE
edOrderNo = @OrderNo'
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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