how to control data types

i have sp that insert fileName and fileType

how can i forbidden in the sp other types then word doc and images?

Code Snippet

if not exists(select Number

from dbo.Freezrelease

where FileName1 like @.FileName1 and

FileType like @.FileType and

FileSize = @.FileSize)

/*{*/ begin

if @.FileSize<500

/*{*/ begin

insert into dbo.Freezrelease(FileName1,FileType,FileSize)

values(@.FileName1,@.FileType,@.FileSize)

select @.@.identity

/*}*/ end

else

select -1

/*}*/end

else

select 0

So you are just inserting the file names, not the bits? If so, just add a line:

if @.fileType not in ('doc','jpg','...etc')

begin

raiserror ('Filetype must be doc, jpg, or etc')

return -100

end

And the procedure will stop there after raising an error

|||

You could add a CHECK constraint to the table that limits the values in the FileName field to only values ending with '.doc', '.docx'*, or '.jpg'. (And of course, any other image file extensions required...)

Using this method, it would not be necessary to 'control' every stored procedure or application query. Programmers and users will not be able to thwart your determination that a 'proper' FileName be supplied. The CHECK constraint would 'police' the inserts/updates for you.

* .docx for Word 2007

Code Snippet

CREATE TABLE #MyTable
( RowID int IDENTITY,
FileName varchar(50)
CHECK ( FileName LIKE '%.doc%' OR FileName LIKE '%.jpg' )
)
GO

-- These succeed
INSERT INTO #MyTable VALUES ( 'MyFile.doc' )
INSERT INTO #MyTable VALUES ( 'MyPic.jpg' )
INSERT INTO #MyTable VALUES ( 'MyFile.docx' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile.txt' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile' )

SELECT *
FROM #MyTable

RowID FileName
-- --
1 MyFile.doc
2 MyPic.jpg
3 MyFile.docx

DROP TABLE #MyTable

|||

What Arnie says is true too, though I would prefer having a fileType column to hold the extension also, for all of the basic normalization reasons:

1. You can add information about types of files easily and join to it without the substring

2. Listing files by type will not require a substring

3. Searching for files by type can be indexed

Really it is all about the need to avoid dealing with parts of a column value.