#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    11

    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. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    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'

IMN logo majestic logo threadwatch logo seochat tools logo