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'

You can convert a coldfusion string to a Java array of bytes using the .getBytes hidden function

<Cfset variables.aBytes = variables.cString.getBytes() />

Converting russian characters too URL hexadecimal characters

The binary code should be of the following format

// The position in the Unicode table tells us how many bytes are needed.
// Note that if we talk about first, second, etc. in the following, we are
// counting from left to right:
//
// Position in | Bytes needed | Binary representation
// Unicode table | for UTF-8 | of UTF-8
// ----------------------------------------------------------
// 0 - 127 | 1 byte | 0XXX.XXXX
// 128 - 2047 | 2 bytes | 110X.XXXX 10XX.XXXX
// 2048 - 65535 | 3 bytes | 1110.XXXX 10XX.XXXX 10XX.XXXX
// 65536 - 2097151 | 4 bytes | 1111.0XXX 10XX.XXXX 10XX.XXXX 10XX.XXXX

You can retrieve the last executed queries from the database using the query

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query],deqs.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

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

Adding elements to a structure

While I was playing with a request structure in coldfusion I come across a strange scenario with adding elements in the structure
There are 3 ways to add an element in the structure

<cfset request.sMyStructre[elementid] = value />

<cfset structInsert(request.sMyStructre,elementid,value) />

<cfset request.sMyStructre.elementid = value />

Even though the result of those 3 commant 99% of the times is the same I found a case where they produce a different output

I create an object test.cfc

<cfcomponent output="true">

<cfset request.sReqObjects1 = structNew() />

</cfcomponent>


and a test cfm page
<cfset request.sReqObjects1 = structNew() /> <Cfset request.sReqObjects1['t2'] = createObject("component","Test") /> using the request.sReqObjects1['t2']<br />
<cfdump var="#request.sReqObjects1#"> <cfset structInsert(request.sReqObjects1,'t3',createObject("component","Test")) /> using the structInsert(request.sReqObjects1,'t3',createObject("component","Test")) <br /> <cfdump var="#request.sReqObjects1#"> <Cfset request.sReqObjects1.t1 = createObject("component","Test") />
using the request.sReqObjects1.t1 <cfdump var="#request.sReqObjects1#">


And the output was
using the request.sReqObjects1['t2']

struct [empty]

using the structInsert(request.sReqObjects1,'t3',createObject("component","Test"))

struct [empty]

using the request.sReqObjects1.t1

struct
T1	component Test

The only reason I can thing for this is that
using [request.sReqObjects1['t2'] =] or [structInsert ] creates the element on a different memory location and just adds a reference to that location in the structure, which then is cleared by the <cfset request.sReqObjects1 = structNew() /> in the cfc constructor. But when using the <Cfset request.sReqObjects1.t1 = the new element is actually created in the memory space of the structure.

ColdFusion 8 and MSSQL triggers

Coldfusion 8 handles MSSQL triggers different that older versions of Coldfusion.

IN older CFMX

We had some For insert triggers on tables for updating related tables etc.. The last trigger used to return the new identity using select SCOPE_IDENTITY() as @iNewRec

the cfquery was

<cfquery name="qInsert" datasource="#myDSN#">
insert into tbl1 (name,fname)
values
('test1','test2')
</cfquery>


Then you could do the

<cfset variables.iNewId = qInsert.iNewRec />

In CFML 8 this will not work. You will get the error that qInsert is Undefined.

The solution is
a) make sure that the select SCOPE_IDENTITY() as @iNewRec is executed before any update/insert delete statement in any trigger in the sequence...

or

b) In the cfquery tag after the insert add a dummy select

<cfquery name="qInsert" datasource="#myDSN#"> insert into tbl1 (name,fname) values ('test1','test2') select 'ttt' as t </cfquery>

DateDiff in coldfusion does not work the same way as dateDiff in sql

DateDiff in coldfusion does not work the same way as dateDiff in sql

I believe Coldfusion gets the different between date1 and date2 in ms and then converts this value to the date part that you specify on the dateDiff function. Sql subtracts the datepart value of date 1 from the date part value of date2.

What does that mean

DateDiff('d','2007/10/02','2007/12/01') in coldfusion is : 1 dateDiff(month,'2007/10/02','2007/12/01') in SQL is : 2 DateDiff('d','2007/10/02','2007/12/04') in coldfusion is : 2 dateDiff(month,'2007/10/02','2007/12/04') in SQL is : 2

To make coldfusion return the same value as SQL use this code

datePart('m',createDate(2007,12,1)) - datePart('m',createDate(2007,10,1))

Calling public methods with in the component without 'this' is faster

I did some testing whether i should use this to call public methods with in the component and it seams that calling a public method with in the component is faster if you do not use the 'this' .

I created 2 simple cfc that were calling internally a public function the one with this.function name and the other one the function name directly not using the this was faster for 15-20 %

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%'

Query Of Queries is case sensitive

I discover recently that Query of queries is case sensitive. to solve the problem i use the function Lcase to convert the coldfusion variables to low case and the query function Lower to convert the query column to low case.


select *
from qQ2
where
lower(column_1) = '#LCase(cF1)#'

ColdFusion 8 Bug - CronService fails with: whith listAll schedule task

This is an error i found on CF 8 using the coldfusion.server.ServiceFactory

When you use the createObject("JAVA","coldfusion.server.ServiceFactory").getCronService().listAll() to get the list of schedule task on the server it doesn't return any newly added schedule tasks that were added with the cfschedule command (it return [undefined array element]) - however this is not the case when you add the schedule task directly in the server.

The solution is to use the load() method before retrieving the list of all schedule task