October 10, 2007 at 4:54 am
I apologise if this is ludicrously easy, but I've searched for the answer and not found it.
Without exactly why I want to do this, I just want to write a loop that cycles through these fieldnames:
popr_cp1, popr_cp2, popr_cp3.......popr_cp10
Here's a rough and basic idea of what I imagined the code to look like:
DECLARE @counter INT, @cost CHAR(6)
SET @counter = 1
WHILE @counter <= 10
BEGIN
SET @cost = 'cost' + STR(@counter)
SELECT [product], [supplier], @cost
FROM suppliercosts
SET @counter = @counter + 1
END
Of course, the code sees the variable @cost as a value, not as a fieldname in the suppliercosts table.
What do I do to tell T-SQL that the variable is a fieldname and not a value?
Be gentle............I'm only little
By the way, I know I can do this with 10 SELECT statements, but I wanted something a little more elegant...........
October 10, 2007 at 5:07 am
If you wish to do it this way then I think your only option is to use dynamic SQL to build your SELECT statement, then either EXEC or sp_executesql to execute it. Please give this a try and post back if there's anything you don't understand.
By the way, I know you're reluctant to say why you want to do it like this, but if you could tell us your exact requirements then we may be able to help you find something that's more efficient/scalable/elegant/etc.
John
October 10, 2007 at 6:08 am
As is said and experienced, dynamic sql is not recommended, however for your question the dynamic sql can accomplish the task.
DECLARE @counter INT, @cost CHAR(6)
DECLARE @sql VARCHAR(2000) -- you can as well use nvarchar
SET @sql = 'SELECT [product], [supplier] '
SET @counter = 1
WHILE @counter <= 10
BEGIN
SET @cost = 'cost' + CAST(@counter AS VARCHAR)
SET @counter = @counter + 1
END
SET @sql = @sql + ' FROM #suppliercosts '
EXEC (@sql )
I don't really recommend using this but just as a quick solution I posted this.
Prasad Bhogadi
www.inforaise.com
October 10, 2007 at 6:47 am
Many thanks John and Prasad for your help. It is very much appreciated.
I have gone away and read about dynamic queries and re-written the code to use that and it works perfectly.
I note what you both say about avoiding it, but it doesn't matter so much in this instance. The code is for converting data from one format to another as part of a conversion project. Once I'm happy with the code, it's a one-off run. So, there are no performance issues to consider.
Thanks again.......I learned something new today......
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply