functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/encyclopedia/l/lu/luhn_algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:
> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> > Hi there,
> >
> > Is it possible to modify a function used in a check constraint, without
> > having to drop the constraint first?
> >
> > E.G.
> > Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> > @.StatusId int) Returns bit As
> > Begin
> > declare @.RetVal bit
> > if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> > Set @.RetVal = 1
> > else
> > Set @.RetVal = 0
> > Return @.RetVal
> > End
> > go
> >
> > Alter table dbo.SampleItemIssue Add Constraint
> > CK_SampleItemIssue_StatusTypeId Check(
> > dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> > )
> > go
> >
> > Alter function dbo.CheckSampleItemIssueStatus(...
> >
> > returns an error along the lines of cannot alter function because it is
> > referenced by constraint..
> >
> >
> > Thanks.
> >
> > Fred.
> >
>
>

functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.
You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>
|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/en...algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:

> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
>
>

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
>