|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
VeriSign Code Signing Digital Certificates provides assurance to end users. Read about this and more in the free white paper: “How to Digitally Sign Downloadable Code for Secure Content Transfer.” Learn More! |
|
#1
January 19th, 2004, 11:40 PM
|
|||
|
|||
|
checking enum values
I want to write a procedure that will check that a variable that is passed in is one of the enumerated values that is allowed.
The line if (Select Where @status IN ('Retired', 'Playing', 'Injured')) is the one that I am trying to figure out a way to do it. I don't have a table that contains all the options, but instead I thought that it would be easier to simply check using an 'IN', but I am not sure if this is possible Thanls for the help Code:
-------------------------------------------------------------------------------------------------
-- isValidStatus
-------------------------------------------------------------------------------------------------
Create Procedure isValidStatus (@status as varchar(10) = null)
As
Begin
if (@status is null or @status = '')
raiserror('isValidStatus - You must pass in a player status',16,1)
else
Begin
if (Select Where @status IN ('Retired', 'Playing', 'Injured'))
Select 1 --return true -> the status exists in the available options
else
Select 0 --return false -> the position does not exist in the available options
End
End
Return
|
|
#2
January 20th, 2004, 01:03 AM
|
|||
|
|||
|
The procedure below should work for you, but you might want to consider putting a check constraint on the table instead. That way every time a row is inserted or updated the values are checked.
The other option is a UDF which can be used inline like any other function call. Hope this answers your questions... Check out Books On Line (BOL) about check constraints... every db should use them. Adam Code:
create table fooBar(
id int identity(1,1) primary key,
stat varchar(10) not null check ( stat IN('Retired', 'Playing', 'Injured') )
)
go
-- this works
insert into dbo.foobar values( 'Retired' )
--this fails
insert into dbo.foobar values( 'NoValue' )
Code:
create function fIsValidStatus (
@status varchar(10)
)
returns integer
as
begin
if exists (select 1 from @status IN('Retired', 'Playing', 'Injured') )
return 1
return 0
end
go
-- test the function
select dbo.fIsValidStatus( 'Retired' )
go
Code:
Create Procedure sprocIsValidStatus (@status as varchar(10) = null)
As
Begin
if (@status is null or @status = '')
raiserror('isValidStatus - You must pass in a player status',16,1)
--else you do not need the else
if exists ( select 1 where @status IN('Retired', 'Playing', 'Injured') )
select 1
else
select 0;
End
go
--test sproc
exec dbo.sprocIsValidStatus 'Retired'
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > checking enum values |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
