Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Concatenate a field name variable in a loop

Author  Topic 

Manjate
Starting Member

3 Posts

Posted - 2012-11-13 : 20:51:37
I have a table with the following fields:

Code Varhar(7)
EXIST1 Float
EXIST2 Float
Exist3 Float
Exist4 Float
...
Exist9 Float

I WANT UPDATE FIELDS BY USING A LOOP.
SOMETHING LIKE:

WHILE N<=9
UPDATE MYtABLE SET EXIST+'N'= EXIST+'N' + nnn.nn
N=N+1
LOOP

How can I Concatenate EXIST and 'N' so that I can have
the name of fields?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-13 : 20:54:32
you can't do that. What is this for ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Manjate
Starting Member

3 Posts

Posted - 2012-11-13 : 23:29:04
IN A table1 I WANT TO BUILD A TRRIGGER THAT WILL UPDATE ONE FIELD OF OTHER table2
where table2 has Fields:
Code Varhar(7)
EXIST1 Float
EXIST2 Float
Exist3 Float
Exist4 Float

table1 updates one field of table2 depending on case 1,2,3,...9
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-13 : 23:47:42
You should consider normalize your table. It will make such things much easier to handle.

Other wise, you might need complex case when statement to handle the updating logic.

Another alternative is to use Dynamic SQL. But that does not look pretty also.




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Manjate
Starting Member

3 Posts

Posted - 2012-11-14 : 12:10:43
i have done, by building this function



declare @code as nvarchar(15)
set @code='003'
declare @Arm int
declare @Result float
set @arm=1
Declare @cmd as varchar(2000)
set @cmd = 'DECLARE RegCur CURSOR for Select exist'+cast(@arm as nvarchar(1)) +' from tb201 where codigo='''+@code +''''
EXEC (@cmd)
OPEN RegCur;
DECLARE @exist float
FETCH NEXT FROM RegCur INTO @exist;
set @total= cast(@exist as Varchar(11))
CLOSE RegCur
DEALLOCATE RegCur;
print @Result
GO
Go to Top of Page
   

- Advertisement -