General database quesion

guys i have a programming question i have table called prerequsites
which is like this
prerequisite component component

a b

so inside my before insert trigger i have to check if the prerequisite is a valid entry like for instance
an entry like b is a prerequisite component for component a which is not a valid entry since i already have an entry in the prerequisite table which defines that comonent a is a prerequisite of b. however this is
very basic so i can test it easy some thing like this in my before insert
prerequisite component component

a b
b a ( Not a valid entry)
select count(*) from prerequisite where component = value of prerequisite component
and prerequisite component = value of component
the above select statement will actually tell me if entry is allowed for insert and does not override other prerequsites

but it can get very complex depending on how the user chooses so if someone can tell me a nice algorithm or good logic to approach this problem it would be great like for instance

prerequisite component component

a b
b c
c a ( Not a valid entry. so how do i ensure that this entry is not allowed considering that complexity for prerequsites can increase quite greatly)

please can someone tell me how to approach this problem.

The database i am using is Oracle 9i but it does not really matter since it is a general database question

This is a directed graph. If you find a cycle in it, then it is an invalid entry.

a<-b // a preceeds b
b<-c // b preceeds c
c<-a // c preceeds a, but this forms a cycle: a<-b<-c<-a

You can use a depth first search or a breadth first search to find the cycle. Any standard algorithms book or website will tell you how to do it. You will need a stack or recursion for DFS or a queue for BFS.

See

http://en.wikipedia.org/wiki/Graph_theory

You can also try a time/space tradeoff and solve the problem in O(1) time without a stack or queue by using a technique called "path enumeration". The idea is to precompute all edges - even those with path length > 1. The problem is the #of edges can grow very large depending on the topology of your graph. But in some cases with relatively "flat" and "bushy" graphs, this might be a very acceptable tradeoff. This was explained in a Joe Celko book.

Regards,
Clifford Dibble

Function inside a store procedure

Is it possible to create a function inside a store procedure use it and at
the end of the procedure drop the function'
Just curious if somebody has made it !!Marco A. Pi?a wrote:
> Is it possible to create a function inside a store procedure use it
> and at the end of the procedure drop the function'
> Just curious if somebody has made it !!
You could...
Create Proc CreateExecDropFunc
as
Begin
Declare @.SQL nvarchar(4000)
Declare @.FuncName nvarchar(36)
Declare @.TestInt int
Set @.FuncName = CAST(NEWID() as nvarchar(36))
-- Watch for line breaks on next line
Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
Exec sp_executesql @.SQL
Print @.SQL
Set @.TestInt = 1
Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
Print @.SQL
Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
Exec sp_executesql @.SQL
End
Go
Exec CreateExecDropFunc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Excellent, very usefull !!!
"David Gugick" wrote:

> Marco A. Pi?a wrote:
> You could...
> Create Proc CreateExecDropFunc
> as
> Begin
> Declare @.SQL nvarchar(4000)
> Declare @.FuncName nvarchar(36)
> Declare @.TestInt int
> Set @.FuncName = CAST(NEWID() as nvarchar(36))
> -- Watch for line breaks on next line
> Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
> Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
> Exec sp_executesql @.SQL
> Print @.SQL
> Set @.TestInt = 1
> Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
> Print @.SQL
> Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
> Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
> Exec sp_executesql @.SQL
> End
> Go
> Exec CreateExecDropFunc
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
Fred
Hi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
--
FredHi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred