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 = '
SET '+@ColumnName+ ' = '+ @ParValue+ ''
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,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
You didn't close the single quote:
SELECT @Sql = '
	SET '+@ColumnName+ ' = '+ @ParValue+ '
	edOrderNo = @OrderNo'

Open in new window

visionetvAuthor Commented:

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.
"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."
That's what a where is for, updating only the line(s) you need.

Why nothing get's updated?  
See what your @sql iooks like before you execute . In place of execute do a
print @sql
Select @sql
See what your result is and if it realy is like you want.  This is a basic step in the testing of your dyamic sql that often reveals something that is covered in the complex construction of the command.

Second debug-rule first write the select that wil return the rows that you need to update. If you see that set is correct (not only on the number of rows affected ) and then convert it into the update.

In this case you will see that Victor gave you hint in which direction to look. You did not close your command string at good location. Only he put the ' after the @OrderNo.  So when you use the print @sql-tip I suggested you will see not the content of that variable is part of your statement but the name of the variable.

	edOrderNo = '''+ @OrderNo+'''''

Open in new window

In this piece you also see I put  quotes around the content of the variable. It is a varchar so my first reaction is ... it should be quoted.    Probably not and same as your @parvalue it probably is a numeric value in the update you test.
Question that pops up is why it is not a numeric type?  And sure you have to convert that to a varchar to concatinate to your dynamic sql.   But it is not type-safe!  And even sql-injection danger looks around the corner.

Other issue I see with this dynamic sql. Are there so much options for columns to update that you make it a dynamic sql?
What if edOrderNo is passed as column name?
 What if a column name with a space is pased?  Putting [] around the column name in dynamic sql  will counter that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
visionetvAuthor Commented:

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,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.