Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Check contraints and TSQL scalar function


Hi,

In a SQL 2005 DB, I've create a scalar function with TSQL that works life
this :

NumberOfSomething(param1 int, param2 int)

the function works correctlyand return NULL or an int.

I've also a table with some columns, including two columns that match param1
and param2 and I'd like to disallow adding new row when the result of the
function returns 1 or more.
I've tried to create a check constraint, but I get errors "Unable to add
constraint 'CK_TableName'.  'NumberOfSomething' is not a recognized built-in
function name."

I'd apreciate any help,
Thanks,
Steve

Try:

create function dbo.NumberOfSomething
(
 @p1 int
, @p2 int
)
returns int
as
begin
 return abs (@p1 - @p2)
end
go

create table t
(
 p1 int
, p2 int
, constraint CK_t check (dbo.NumberOfSomething (p1, p2) < 1)
)
go

insert t values (1, 1)
insert t values (2, 2)
go

insert t values (1, 2)

drop table t
go

drop function dbo.NumberOfSomething

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Steve B." <steve_bea@com.msn_swap> wrote in message

news:uBTPapeoHHA.4120@TK2MSFTNGP06.phx.gbl...
Hi,

In a SQL 2005 DB, I've create a scalar function with TSQL that works life
this :

NumberOfSomething(param1 int, param2 int)

the function works correctlyand return NULL or an int.

I've also a table with some columns, including two columns that match param1
and param2 and I'd like to disallow adding new row when the result of the
function returns 1 or more.
I've tried to create a check constraint, but I get errors "Unable to add
constraint 'CK_TableName'.  'NumberOfSomething' is not a recognized built-in
function name."

I'd apreciate any help,
Thanks,
Steve

-----------------------------------------------Reply-----------------------------------------------

I finally found... I needed to add "dbo." before the name of the function in
the check expression...

Thanks any else

Steve

"Steve B." <steve_bea@com.msn_swap> wrote in message

news:uBTPapeoHHA.4120@TK2MSFTNGP06.phx.gbl...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc