SQL prime numbers

The following function returns the prime numbers of a list of number


CREATE FUNCTION dbo.PrimeNumbers
(
@NumberList nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN

declare @pdv as nvarchar(150)

set @pdv = ''

select @pdv = @pdv + value + ','
from (
select s.value,
case when exists(select 1
from dbo.fn_t_dstring2table(@NumberList, ',') d
where cast(d.value as numeric(18,2)) < cast(s.value as numeric(18,2))
and cast(s.value as numeric(18,2)) % cast(d.value as numeric(18,2)) =0) then 1
else
0
end as divider
from dbo.fn_t_dstring2table(@NumberList, ',') s) as t
where t.divider = 0

return left(@pdv,len(@pdv)-1)

END


Example

select dbo.fn_v_get_PrimeNumbers('2,3,4,6,8,9,10,11,15')

return 2,3,11

Convert Arabic numbers in SQL 2005

The following function converts arabic numbers (nvarchar) to digital numbers

Alter Function [dbo].[Convert_Arabic_To_Decimal]

(

@String nvarchar(100)

)

Returns numeric(20,6)

as

BEGIN

declare @String2 nvarchar(100)

declare @rtrn as numeric(20,6)

declare @iMode as smallint

declare @bMode as smallint

declare @Digit as numeric(18,6)

set @String2 = @String

set @rtrn = 0

set @iMode = 1

set @bMode = 1

while @String2 <> ''

Begin

if (unicode(left(@string2,1)) >=1632 and unicode(left(@string2,1)) <=1641)

or (unicode(left(@string2,1))=1776)

begin

set @Digit = unicode(left(@string2,1))-1632

if unicode(left(@string2,1))=1776

set @Digit = 0

if @bMode =1

set @rtrn = (@rtrn * power(10,@iMode)) + ( @Digit)

else

begin

set @rtrn = @rtrn + ( @Digit / power(10,@iMode))

set @iMode = @iMode + 1

end

end

else if unicode(left(@string2,1)) = 46

set @bMode = 0

set @String2 = right(@string2,len(@string2)-1)

End

return @rtrn

END

Example:

declare @String as nvarchar(100) set @String = N'٧.۰٧٨٠٧' select [dbo].[Convert_Arabic_To_Decimal](@String)

returns 7.078070

Arabic numbers in SQL 2005

The arabic numbers in SQL 2005 have unicode between 1632 - 1641 where 1632=0 and 1641=9, whoever when converting arabic numeric string to digit number the system usually confuse the NChar(1632) with NChar(1776), both character they look the same

listFind function in MS SQL 2005

The following function locates the position of an element in a delimited list

ALTER FUNCTION [dbo].[listFind]

(

@List nvarchar(max),

@Element nvarchar(150),

@Delimiter nvarchar(10) = ','

)

RETURNS smallint

AS

BEGIN

declare @IndexPos int

declare @CurrentElement nvarchar(150)

declare @I int

declare @TemList nvarchar(max)

set @TemList = @list

if (len(@List)<=0)

return 0

set @IndexPos = charIndex(@Delimiter,@List,0)

set @I = 0

while (@IndexPos <> 0 and @TemList <> '')

Begin

set @I = @I + 1

set @IndexPos = charIndex(@Delimiter,@TemList,0)

if (@IndexPos <> 0)

begin

set @CurrentElement = substring(@temlist,0,@IndexPos)

set @TemList = substring(@TemList,@IndexPos+1,len(@TemList))

end

else

set @CurrentElement = @TemList

if (@CurrentElement = @Element)

return @i

end

if (@CurrentElement = @Element)

return @i

return 0

END

for example

select [dbo].[fn_v_listFind]('dog,cat,fish,cow','dog',',')

return 1

select [dbo].[fn_v_listFind]('dog,cat,fish,cow','bird',',')

return 0

listGetAt functions in SQL 2005

The following list return the element at the X position in a delimited sting list

ALTER FUNCTION [dbo].[listGetAt]

(

@List nvarchar(max), -- delimited list

@ElementPos int, -- the element position

@Delimiter nvarchar(10) = ',', -- the delimiter string

@ReturnOnNotFound nvarchar(100) = null -- the default value if element does not exist

)

RETURNS nvarchar(100)

AS

BEGIN

declare @StrartPos int

declare @IndexPos int

declare @CurrentElement int

declare @TemList nvarchar(max)

set @TemList = @list

if (len(@List)<=0)

return @ReturnOnNotFound

set @StrartPos = 0

set @IndexPos = charIndex(@Delimiter,@List,@StrartPos)

set @CurrentElement = 0

while (@IndexPos <> 0 and @CurrentElement<@ElementPos and @TemList <> '')

Begin

set @CurrentElement = @CurrentElement + 1

set @IndexPos = charIndex(@Delimiter,@TemList,@StrartPos)

if (@IndexPos <> 0 and @CurrentElement<@ElementPos)

set @TemList = substring(@TemList,@IndexPos+1,len(@TemList))

end

if (@CurrentElement<@ElementPos)

return @ReturnOnNotFound

if @IndexPos > 0

return substring(@temlist,0,@IndexPos)

return @temlist

END

For example:

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',2,',','not found')

returns 'cat'

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',1,',','not found')

returns 'dog'

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',7,',','not found')

returns 'not found'

define trigger execution order

sp_settriggerorder use for defining the After trigger execution order. You can specify only the first trigger to be executed and the last trigger to be executed.

the syntax of the command is

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
       , [ @order = ] 'value(First or Last)'
       , [ @stmttype = ] 'statement_type ( Insert or update or delete)'
       [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

Note that after executing the sp_settriggerorder if you edit your trigger you will notice the call to the sp_settriggerorder
store procedure at the bottom of the trigger. This is because the trigger execution sequence can change any time a trigger is Alter  
For http://msdn2.microsoft.com/en-us/library/ms186762.aspx

Note

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.

If the same trigger must be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for that statement type.

Update from

You can use the Update from statement to update tables of one table from another

for example you have a table Employee_old_table (employee_id,employee_name,employee_lname)
and e table employee_new_table (employee_id,employee_full_name,employee_first_name,employee_last_name)

to set the employee order.employee_full_name = employee.employee_name + ' ' + employee_lname

you can do this

update employee_full_name
set
employee_full_name = e.employee_name + ' ' + e.empoyee_lname,
employee_first_name = e.employee_name,
employee_last_name = e.employee_lname
from employee e
where e.employee_id = employee_id

instead of

update employee_full_name
set
employee_full_name = (select e.employee_name + ' ' + e.empoyee_lname from employee e where e.employee_id = employee_id),
employee_first_name = (select e.employee_name from employee e where e.employee_id = employee_id),
employee_last_name = (select e.employee_lname from employee e where e.employee_id = employee_id)

Return a value from a strore procedure value

Store procedures can return value using a return statement in the store procedure

you can do something like this

create PROCEDURE dbo.test1
(
@iID int
)
AS
BEGIN
SET NOCOUNT ON;

Declare @iNewId int

set @iNewId = @iID + 1
return @iNewId
END

declare @iT int
exec @iT = dbo.test1 6

print @iT

MSSQL, - 2005 - search in the code of Store procedure,function, views and triggers

We had an sql function that we didn't know if is used for anywhere, so we decided to remove it. There was no reference to the store procedure in our code so the only place remaining was the sql store procedures, functions and triggers and views

To do this we use the sql query

select *
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION','VIEW','SQL_STORED_PROCEDURE',
'SQL_SCALAR_FUNCTION','SQL_TRIGGER')
and OBJECT_DEFINITION (object_id) like '%MinPricePreCovered%'