--對于表的遍歷在很多時候都要用到, 下面給出了兩種方法declare @table table(code int,msg nvarchar(15));--聲明表作為遍歷對象declare @code varchar(15); declare @msg varchar(20);declare @i int ;set @i=1; while (@i<100000)--得到表的總行數begininsert into @table(code,msg) values(@i,'我是'+cast(@i as char(6)))set @i=@i+1;end --使用游標遍歷表declare @curl cursor;set @curl =cursor forward_only static forselect code ,msg from @table;open @curl--開啟游標fetch next from @curl into @code,@msg;while(@@FETCH_STATUS=0)beginPRint @code;fetch next from @curl into @code,@msg;end close @curl;--關閉游標deallocate @curl;--刪除游標--使用函數遍歷while exists(select top 1 code from @table)beginselect top 1 @code=code,@msg=msg from @tableprint @code;delete from @table where code=@codeend--在實際中據說函數的遍歷比游標要快,而且內存占用較少.但是自己沒有接觸過大數據,所以在實際中怎么樣也不得而知.--在本例中 由于函數要查詢和刪除 游標速度較快
/*sql 語句拼接*/--200個0-10的隨機數 拿隨機數和7相處，如果余數是0 c0的值+1，最后得到200個數字出去7余數是幾的和create  table test  (stat char(1), --在拼接的時候 對于如何使用臨時表 還是不會  所以在這里創建表c0 int,c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,num varchar(500));insert into test values('N',0,0,0,0,0,0,0,'');declare @i int; set @i =0; declare @remainder  int ;declare @num int ; declare @sql nvarchar(100)--注意一定要是nvarcharwhile(@i<200)beginset @num=(cast ( rand ()*10  as int ));set @remainder=@num%7;set @sql=N'update test set c'+CAST(@remainder as char (1))+' =c'+CAST(@remainder as char (1))+'+1,num=num+'''+cast (@num  as char(1))+''' where stat=''N''';--N'string' 表示string是個Unicode字符串print @sql;exec sp_executesql @sqlset @i=@i+1;endselect * from test