A tough nut to crack

I have a class C# which dynamically generates the SQL to create a stored procedure, one of the parameters i write into to the SQL is @.Keyname, and i use it in the following way

WHERE @.KeyName = CONVERT(VARCHAR, @.KeyValue)

However when i run the generated SQL to actually create the stored proc is is created with the line exactly as it is above. I need to be able to take the keyname as specified when the proc is called, which is a quoted string (i.e 'keynamefield' and write it into the SQL as keynamefield without quotes to make the field lookup dynamic based on this keyname field.

I am presuming that i will need to use function against the keyname variable inside the stored procedure (as this cannot be called from C#) something like GetValue(@.KeyName)

??

Any Ideas would be greatly appreciated

You need to use dynamic SQL but that is not what you want to do. You should not write applications that passes column names and table names dynamically for manipulation. There are lot of security risks, performance issues among other things. Best is to create the SP in such a manner that you don't need dynamic SQL. There are many ways to do this. The link below discusses the techniques to do something like this:

http://www.sommarskog.se/dyn-search.html

|||

As Umachandar said, its highly risk to create a object from your code. Your database is widly open to any one. Security Issues..

Comming to your issue, you have to use NVARCHAR instead of VARCHAR for unicode characters(non-english alphabets).

|||

Got it sussed thanks, the procs use the sql in a pre-generated fashion (i.e the compiler calculates the select and then it is added to a stored proc and which sql is executed is controlled by paremeters.) The procs are also encrypted so no one can execute any code they fancy on the database. I wonder though, can you encrypt tables the same way toy can procedures (i.e. WITH ENCRYPTION)?

Thanks

|||No. You can't encrypt the table definitions.