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);
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.
18 People are following this question.
