question

muhammadgulfam avatar image
muhammadgulfam asked

how to add custom column in SQL string query with variable

I am using string query in sql and executing it with Execute method. I have to add an additional column with default value. that default value is contained in a variable. When i add that default valued column in the query string and execute it I get an error "Invalid column name 'dafaultValue' ". Is there any other solution to do this ? Here is my code: Declare @variable Varchar(Max); Declare @variableQuery Varchar(Max); select @variable = 'Test'; set @variableQuery = 'select '+@variable+' as dest ,* from myTable '; execute (@variableQuery);
sqlquerystring
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered
As your @variable is text, your adding text to the string and thus not enclosing it in ' This is what your basically executing select Test as dest ,* from myTable What you need to do is to add ' to the start and end of the string Something like this Declare @variable Varchar(Max); Declare @variableQuery Varchar(Max); select @variable = 'Test'; set @variableQuery = 'select '''+@variable+''' as dest ,* from myTable '; select @variableQuery execute (@variableQuery); Which gives you this select 'Test' as dest ,* from myTable I would also move away from this sort of dynamic query as you leave yourself open to SQL Injection, where I could easily inject malicious SQL code and destroy your databases.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.