[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

constraints and udfs

John Grandy

3/8/2007 11:33:00 PM

Apparently it is not possible to call UDFs from constraints.

Or is there a clever workaround ... ?


3 Answers

Tom Moreau

3/8/2007 11:44:00 PM

0

Not so:

create function dbo.fx
(
@a int
, @b int
)
returns bit
as
begin
if (@a > @b)
return cast (1 as bit)

return cast (0 as bit)
end
go

create table t
(
ID int primary key
, x int not null
, y int not null
, check (dbo.fx (x, y) = 1)
)
go

drop table t
go

drop function fx



--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"John Grandy" <johnagrandy-at-gmail-dot-com> wrote in message
news:OkGqlodYHHA.4440@TK2MSFTNGP03.phx.gbl...
Apparently it is not possible to call UDFs from constraints.

Or is there a clever workaround ... ?

--CELKO--

3/9/2007 5:07:00 AM

0

>> Apparently it is not possible to call UDFs from constraints. <<

Yes, you can write such code which will be proprietary, useless to the
optimizer and a screaming bitch to maintain. Look at Tom's BIT
flags -- straight out of 1950-'s assembly language coding and he knows
better!

>> Or is there a clever workaround ? <<

No, just stick to good, solid SQL programming and put declarative code
into CHECK() clauses.

For the most part CHECK() constraints are simple predicates with
theta, IN(), BETWEEN and LIKE operators. That is 95% of your work in
the real world

If you need tricky logic, use a CASE expression in a CHECK () like:

CHECK (CASE WHEN <pred_1> THEN 'T' .. ELSE 'F' END = 'T')

Or use library functions such as REPLACE() UPPER(), LOWER() , etc. to
check stringhs. .

Worst case, there are some tricks with VIEWs adn the WITH CHECK OPTION
that will let you reference other tables.That happens < 0.01% of the
time.

It takes at least a year to switch from procedural thinking to set-
oriented declarative thinking

AK

3/9/2007 2:25:00 PM

0

On Mar 8, 5:33 pm, "John Grandy" <johnagrandy-at-gmail-dot-com> wrote:
> Apparently it is not possible to call UDFs from constraints.
>
> Or is there a clever workaround ... ?

Possible, but not a good idea. Discussed here:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/7c80d44912cbff48/e8324b1912c6ca3b?lnk=gst&q=false+sense+of+security&rnum=1&hl=en#e8324b...

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blo...
http://sqlserver-puzzles.blo...