check constraint on input parameter

New to SQL server 2000 and wondering if it's possible to define a check
constraint on an input parameter
(stored procedure).
My input parameter(defined as smallint) should only accept the values 0, 1
or 2.
Would appreciate any help.
Message posted via http://www.webservertalk.comI dont think it is possible. But what you can do instead is the have a
check in the stored procedure and raise an error if the value passed is
not one of the expected values.|||you'd have to either
1) raise an error at the top of the procedure
2) default to 0 for <0 or 2 for >2
e.g.
create procedure myproc @.input1 smallint as
if @.input1 not between 0 and 2
begin
raiserror('Procedure myproc requires parameter @.input1 to be between
0 and 2', 16, 1)
return -1
end
...
or
create procedure myproc @.input1 smallint as
select @.input1 = case when @.input1<0 then 0 when @.input1>2 then 2 else
@.input1 end
...
Cismail via webservertalk.com wrote:
> New to SQL server 2000 and wondering if it's possible to define a check
> constraint on an input parameter
> (stored procedure).
> My input parameter(defined as smallint) should only accept the values 0,
1
> or 2.
> Would appreciate any help.
>|||That's what I thought.
Thanks for your help.
SQL novice wrote:
>I dont think it is possible. But what you can do instead is the have a
>check in the stored procedure and raise an error if the value passed is
>not one of the expected values.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1