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.
