Madhivanan

Dropping all temp tables

 In SQL Forum one of the posters asked a question about dropping all the temporary tables
There are many ways to do this
One of the ways to to run the following code

declare @sql varchar(8000)
set @sql=''
select @sql = @sql+' drop table '+name from tempdb..sysobjects where xtype='u'
exec(@sql)

Cursor statuses

Here are the queries that findout the cursors based on their statuses

select
        cur.cursor_name
from 
        sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle

Generate Fibonacci series - No loop, no recursion

Generating the Fibonnaci series generally involves in writing the code which has while loop or a recursive function

Peso posted the code using the Common table expression

Here are my methods that involve no loop or no recursion

1 Use Quirky update technique

declare @fib table(fib_id bigint,fib_val bigint)

declare @current_val bigint, @previous_val bigint,@new_val bigint

insert into @fib(fib_id,fib_val )

select top 30 row_number() over(order by name),1 from sysobjects

update @fib
set
        fib_val =@current_val,
        @current_val =@new_val +@previous_val,
        @previous_val=@new_val, 
        @new_val=@current_val

select * from @fib

2 Use Generalised formula

Method 1

select 
        floor(power((1+SQRT(5))/2.0,number )/sqrt(5)+0.5) as fib_number
from 
        master..spt_values
where 
        TYPE='p' and number between 0 and 100