Help with Insert with Case. 1

[sparkbyte]

I am stuck on creating a Stored Procedure that has a CASE in it for validataing file numbers.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ======================================================================
-- Author:		John F Fuhrman
-- Create date: 10/12/2010
-- Description:	Stored Procedure for Data Input into the Tracking Table.
-- ======================================================================

CREATE PROCEDURE usp_TrackingInput 
	-- Add the parameters for the stored procedure here
	@FileNumber VarChar = Null,
	@BoxNumber VarChar = Null,
	@TrackingDate DateTime = GetDate()
AS
BEGIN
	SET NOCOUNT ON;

Insert Into dbo.tblTrackingTable (EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)
	Values
		Case 
			When @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
				(PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),Host_Name(),@FileNumber,@BoxNumber,@TrackingDate)
		Case 
			When @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
				(PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),Host_Name(),@FileNumber,@BoxNumber,@TrackingDate)
		Case
			When @FileNumber like '[a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
				(PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),Host_Name(),@FileNumber,@BoxNumber,@TrackingDate)
		Case
			When @FileNumber like '[a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
				(PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),Host_Name(),@FileNumber,@BoxNumber,@TrackingDate)
		Case
			When @FileNumber like '.BOX.END.' 
				(PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),Host_Name(),@FileNumber,@BoxNumber,@TrackingDate)
		Else 
			RAISERROR(50500,16,1)
END
GO

Output

Msg 102, Level 15, State 1, Procedure usp_TrackingInput, Line 12
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Procedure usp_TrackingInput, Line 19
Incorrect syntax near the keyword 'Case'.

Thanks

John Fuhrman

 

[gmmastros]

Honestly... I nothing about MS Access ADP's. Sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

how difficult would it be to alter this so that it tests the file numbers and exits with a true flase value rather than inserting the new row of data?

Thanks

John Fuhrman

 

[gmmastros]

Super easy. I'd be tempted to write it as a user defined function so that I could reuse the code in several places.

Something like this:

Create Function dbo.IsFileNumberValid
	(@FileNumber VarChar(15))
Returns Bit
As
Begin
	Declare @Prefix VarChar(15)
    Declare @IsValid Bit

	Select @IsValid = 0,
           @Prefix = Left(@FileNumber, PatIndex('%[^a-z.]%', @FileNumber + '1')-1)

    If (@FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '[a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '[a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
        OR @FileNumber like '.BOX.END.')
        If Exists(Select 1 from tblFileNumPrefix Where FileNumPrefix = @Prefix)
            Set @IsValid = 1

    Return @IsValid
End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom