Cari Blog Ini

31 Oktober 2013

How To Create Flexible Variable Using Cursor in SQL





Apply To : Microsoft SQL Server 2008 R2


1. Example Table


2. Try to run below code,
------------------------------------------------------------------------------
------------------------------------------------------------------------------
declare @var varchar(100), @a_Branch integer 

declare cur cursor for 
select * from Temp_Counter


open cur
fetch next from cur into @a_Branch

while @@fetch_status = 0
begin
set @var='VAR_'
set @var=@var+convert(varchar(2),@a_Branch)
 
print (' PRINT ---> Declare @'+@var+' varchar(100) set @'+@var+'=''132456'' ')
 
exec ( 'Declare @'+@var+' varchar(100) 
set @'+@var+'=''132456'' 
print ''Results : '' 
print @'+@var+' ')

fetch next from cur into @a_Branch
end

close cur
deallocate cur

------------------------------------------------------------------------------
------------------------------------------------------------------------------




3. Results :