Simple TSQL Scripting -1: Generating Prime Numbers

Create the procedure as follows:

create procedure primegen(@k integer)
as
declare @a int
declare @b int
declare @p int
declare @i int
declare @n int
set @a =2
set @n =1
set @p = @k
set @i =2
print @a
while @n < @p - 1-- Checking for the number of terms
begin --1
set @i = 2
set @a = @a + 1 --incrementing the prime number
while(@i < @a) -- checking whether the dividend is less than the divisor
begin --2
if(@a % @i) <> 0 --- whether the modulo is not zero,if not zero increment i for the next dividend
begin --3 --and check the same modulo..Repeat it till the dividend one less than the divisor
set @i = @i +1
if @i = @a --check whether all the dividends have been divided whose modulo is not zero and print the
begin -- 4 number which is prime
print @a
set @n = @n + 1 -- increment the number of prime terms
break
end --4
end--3
else
begin--5 --if its modulo is zero increment the dividend and come out of the if modulo checking loop
set @i = @i + 1
break
end--5
end--2
end--1

--Execute the First 100 prime numbers

Exec primegen 100

Using System Tables 1 : Oh!!! which processes are taking most CPU time

Master...sysprocesses

This command gives you an inside depth of which process is running the CPU process very high

SELECT * FROM master..sysprocesses WHERE status = 'runnable' ORDER BY cpu desc

When you get the spid you can get the input command from the following

DBCC INPUTBUFFER(spid)

which gives you what statement the end user has been running.

KILL spid would close or kill the process automatically

The other notable columns in this table are

• nt_username
• loginname
• hostname

About me

Hi,My Full name is Vivekanand Serou.Friends and family call me Vic.I am currently working as a Database Administrator in Uswitch(DBA). I will be posting the various stuff i learnt in IT as well as in Relational Databases.
If you have any comments you can mail me to svicky9@yahoo.com