[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Variable Column Name in Dynamic Query

Posted on 2009-04-22
1
Medium Priority
?
500 Views
Last Modified: 2012-05-06
Hello Experts,

I have created a dynamic query, but inside that I am using a variable for the column name.  It is not resolving the variable (@field_to_correct) to the column name.  Not sure how to resolve this.

thanks for any help
declare	  @current_process		nvarchar(max)
		, @current_process_sql	nvarchar(max)
		, @roll_id				int	
 
--variables for cr / lf select and correction
		, @find_cr_lf 				nvarchar(max)
		, @correct_cr 				nvarchar(max)
		, @correct_lf 				nvarchar(max)
		, @find_bad_zip				nvarchar(max)
		, @correct_bad_zip			nvarchar(max)
		, @ParmDefinition 			nvarchar(500)
		, @field_to_correct_value	varchar(100)
 
 
 
 
--grab roll id
select @roll_id 			= (select max(roll_id) from hcad..rolls_diary)	
 
 
--document current point in process
select @current_process 	= 'start correct embedded CR LF -- ' + convert(varchar(max), getdate(), 100) + '; '
select @current_process_sql	= 'UPDATE hcad..rolls_diary SET progress = progress + ''' + @current_process + ''' WHERE roll_id = ''' + cast(@roll_id as varchar(10)) + ''' ' 
 
exec (@current_process_sql)
 
 
 
 
--
--fix fields with cr/lf in pcr_history.  store the information in hcad..rolls_correct_crlf for review and to fix on pawnee
--
 
 
--build the sql strings
set @find_cr_lf = N'insert into hcad..rolls_account_to_correct (roll_id, strap, error_field, crlf_position, field_value)
				    select @roll_id_number, strap, ''@field_to_correct'', charindex(char(13),@field_to_correct,1), @field_to_correct
				 	  from history.dbo.pcr_history
					 where charindex(char(13),@field_to_correct,1) > 1'
 
 
set @correct_cr = N'update history.dbo.pcr_history
					   set @field_to_correct = stuff(@field_to_correct,charindex(char(13),@field_to_correct,1),1,'' '') 
					 where charindex(char(13),@field_to_correct,1) > 1'
 
 
set @correct_lf = N'update history.dbo.pcr_history
					   set @field_to_correct = stuff(@field_to_correct,charindex(char(10),@field_to_correct,1),1,'' '') 
					 where charindex(char(10),@field_to_correct,1) > 1'
 
 
set @ParmDefinition = N'  @field_to_correct	varchar(100)
						, @roll_id_number	int	'
 
 
--set field to update
set @field_to_correct_value = 'ponal'
 
 
exec sp_executesql @find_cr_lf, @ParmDefinition, @roll_id_number = @roll_id, @field_to_correct = @field_to_correct_value;
exec sp_executesql @correct_cr, @ParmDefinition, @roll_id_number = @roll_id, @field_to_correct = @field_to_correct_value;
exec sp_executesql @correct_lf, @ParmDefinition, @roll_id_number = @roll_id, @field_to_correct = @field_to_correct_value;
 
 
 
--set field to update
set @field_to_correct_value = 'dba_name'
 
exec sp_executesql @find_cr_lf, @ParmDefinition, @roll_id_number = @roll_id, @field_to_correct = @field_to_correct_value;
exec sp_executesql @correct_cr, @ParmDefinition, @roll_id_number = @roll_id, @field_to_correct = @field_to_correct_value;
exec sp_executesql @correct_lf, @ParmDefinition, @roll_id_number = @roll_id, @field_to_correct = @field_to_correct_value;

Open in new window

0
Comment
Question by:thewayne73
1 Comment
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 1500 total points
ID: 24210429
Sorry, but you cannot have the ddl elements (tables, columns) as variables passed to the exec statements, just like you can't do that in stored procedures.  You will have to set your variable first and then build the sql statement using it at that time instead of passing it to the exec.
--set field to update
set @field_to_correct_value = 'ponal'
 
set @correct_cr = N'update history.dbo.pcr_history
					   set @field_to_correct = stuff('+@field_to_correct+N',charindex(char(13),'+@field_to_correct+N',1),1,'' '') 
					 where charindex(char(13),'+@field_to_correct+N',1) > 1'
 

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question