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

 

[SgtJarrow]

None of your CASE statements have a THEN...

CASE
WHEN something THEN do something
WHEN something THEN do something
ELSE do something
END

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[bborissov]

You should check the value BEFORE you try to insert it into the table (and you missed one END):

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;
    DECLARE @InsertedValue varchar(8000)
    IF @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = (PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)

    ELSE IF @FileNumber like @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)

    ELSE IF @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @FileNumber like @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @FileNumber like @FileNumber like '.BOX.END.'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    Else
       BEGIN
          RAISERROR(50500,16,1)
          RETURN
       END

    Insert Into dbo.tblTrackingTable (EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)
    VALUES
    (@InsertedValue,Host_Name(),@FileNumber,@BoxNumber,@TrackingDate)
END

(this is ALSO not tested ))))))))


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[sparkbyte]

Close but still having a problem.

CREATE PROCEDURE usp_TrackingInput
    -- Add the parameters for the stored procedure here
    @FileNumber VarChar = Null,
    @BoxNumber VarChar = Null,
    @TrackingDate DateTime = Null
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @InsertedValue varchar(18)
    IF @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = (PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @FileNumber like '.BOX.END.'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    Else
       BEGIN
          RAISERROR(50500,16,1)
          RETURN
       END
Select @TrackingDate = GetDate()
    Insert Into dbo.tblTrackingTable (EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)
    VALUES 
		(@InsertedValue,Host_Name(),@FileNumber,@BoxNumber,@TrackingDate)
End

[red]
Msg 156, Level 15, State 1, Procedure usp_TrackingInput, Line 12
Incorrect syntax near the keyword 'ELSE'.
[/red]

Thanks

John Fuhrman

 

[gmmastros]

You are missing a close parenthesis.

CREATE PROCEDURE usp_TrackingInput
    -- Add the parameters for the stored procedure here
    @FileNumber VarChar = Null,
    @BoxNumber VarChar = Null,
    @TrackingDate DateTime = Null
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @InsertedValue varchar(18)
    IF @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = (PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)[!])[/!]
    ELSE IF @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @FileNumber like '.BOX.END.'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    Else
       BEGIN
          RAISERROR(50500,16,1)
          RETURN
       END
Select @TrackingDate = GetDate()
    Insert Into dbo.tblTrackingTable (EmployeeID, MachineName, [!]BoxNumber, FileNumber[/!], TrackingDate)
    VALUES 
        (@InsertedValue,Host_Name(),[!]@FileNumber,@BoxNumber[/!],@TrackingDate)
End

I also encourage you to triple check the final insert. It appears as though you have your parameters out of order. In the column list for your insert, you have BoxNumber then FileNumber. In the values, you have @FileNumber and then @BoxNumber.

-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]

Two Questions?

Why did you change the structure to an IF else from Case?

and can you explain the logic behind your SP?

Here is the corrected for my use version
with issues still.

USE [MailroomTracking]
GO
/****** Object:  StoredProcedure [dbo].[usp_TrackingInput_v1]    Script Date: 10/13/2010 12:37:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_TrackingInput_v1]
    -- Add the parameters for the stored procedure here
    @FileNumber VarChar(15) = Null,
    @BoxNumber VarChar(50) = Null,
    @TrackingDate DateTime = Null
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @InsertedValue varchar(18)
    IF @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = (PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1))
    ELSE IF @FileNumber like '[a-zA-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @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]'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    ELSE IF @FileNumber like '.BOX.END.'
       SET @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1)
    Else
       BEGIN
          RAISERROR(50500,16,1)
			Print @FileNumber
          RETURN
       END
Select @TrackingDate = GetDate()
    Insert Into dbo.tblTrackingTable (EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)
    VALUES 
        (@InsertedValue,Host_Name(),@BoxNumber,@FileNumber,@TrackingDate)
End

Error

DECLARE @RC int
DECLARE @FileNumber varchar(15)
DECLARE @BoxNumber varchar(25)
DECLARE @TrackingDate datetime

-- TODO: Set parameter values here.

EXECUTE @RC = [MailroomTracking].[dbo].[usp_TrackingInput_v1] 
   @FileNumber = 'A1234578'
  ,@BoxNumber = 'nbc1111111111111'
  ,@TrackingDate = '01/01/2010'

Response is the same no matter what is input for the file number.

[red]
Msg 50500, Level 16, State 1, Procedure usp_TrackingInput_v1, Line 22
The File Number you have entered does not meet existing criteria and will not be Accepted.
[/red]
A1234578


This reason I am asking is because what I am trying to accomplish to have a way to display custom error messages in the UI application, in this case MS Access ADP.

What I had done to stop the bad file number entries was to put a constraint on the table for the filenumber.

Here is the contraint that seemed to work.

USE [MailroomTracking]
GO
ALTER TABLE [dbo].[tblTrackingTable]  
	WITH NOCHECK 
		ADD  CONSTRAINT [CK_tblTrackingTable-FileNumber] 
			CHECK  
				(( [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.' 
				AND [FileNumber] IS NOT NULL))
GO
ALTER TABLE [dbo].[tblTrackingTable] CHECK CONSTRAINT [CK_tblTrackingTable-FileNumber]

This created a new issue, almost worse that the original problem. Access display the generic error from SQL server saying that the file number entered failed the contraint. But the error popup only has OK button.

If the user does not see the error popup and continues scanning the barcodes of the items the next scan clears the popup and then the following scan overwrites the bad scan efectively missing one of the files.

So the idea is to funnel all data input through stored procedures that validate and raise custom error levels depending on failed validations.

I hope this is an effective approach to solving this issue and keeping this type of issue from kreeping up again and provides a less user impacting way of correcting simular issues in the future.

Any comments on this thinking are welcome!!



Thanks

John Fuhrman

 

[gmmastros]

In many front end languages, there is little difference between if/elseif/else and select case. In fact, you could argue that the only real difference is in syntax, and it all compiles to the same thing.

This is certainly not true for SQL Server. In T-SQL If/else is used to control logic and Select Case is used to control data. Sometimes the lines get a little blurry.

Let's take an easier example...

Suppose we had @Fruit parameter, and based on the data, we wanted to set an @color variable. We could do it several different ways.

[tt]
Select @Color = Case When @Fruit = 'Orange' Then 'Orange'
When @Fruit = 'Apple' Then 'Red'
When @Fruit = 'Banana' Then 'Yellow'
Else NULL END
[/tt]

In the example shown above, notice that @Color exists in the code just once, but it's value depends on the case/when statement. We could also have written it like this...

[tt]
If @Fruit = 'Orange'
Set @Color = 'Orange'
Else If @Fruit = 'Apple'
Set @Color = 'Red'
Else If @Fruit = 'Banana'
Set @Color = 'Yellow'
Else
Set @Color = NULL
[/tt]

In this example, we are using IF/Else If/Else to control logic (not just data). I know this is a subtle distinction, but it's important.

Back to your situation. Raising an error falls in to the "controlling the flow of logic" more so than it is "controlling data".



-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]

Thanks!!! I believe I get the point you are making.

So with that said, the If/Esle If/Else would be meet the objective. Using the above code, can you see what I am missing in regards to it always raising the error level even when the file number meets propper criteria?



Thanks

John Fuhrman

 

[gmmastros]

Personally, I don't like the way the code is structured. I prefer to do my data validation first, and then other code later. So, I would have done something like this...

ALTER PROCEDURE [dbo].[usp_TrackingInput_v1]
    -- Add the parameters for the stored procedure here
    @FileNumber VarChar(15) = Null,
    @BoxNumber VarChar(50) = Null,
    @TrackingDate DateTime = Null
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate the incoming data

    If Not(@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.')
      BEGIN
        RAISERROR(50500,16,1)
        Print @FileNumber
        RETURN
      END

    -- We passed validation, now insert the data.

    DECLARE @InsertedValue varchar(18)
    SELECT @InsertedValue = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),
           @TrackingDate = GetDate()
    
    Insert Into dbo.tblTrackingTable(EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)
VALUES(@InsertedValue,Host_Name(),@BoxNumber,@FileNumber,@TrackingDate)
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

 

[sparkbyte]

That looks great!!

I noticed that you changed the If/Else If/Else block to a single If Not. That is much more what i was trying to accomplish. I also see that you sourounded the entire If Not condition in Parans. Why is this? this is the first example I have seen this way.

Very clean code and easy to follow!!!

THANKS!!!!!!!!

Thanks

John Fuhrman

 

[gmmastros]

Well... I noticed that each "positive" match had the same code (setting the @InsertedValue). Truth is, the parenthesis may not have been necessary, but I put them in there to make the logic a little clearer (whether it was necessary or not). Think about this...

If you have a bad apple, you want to throw it away. Unfortunately, the only way we have for testing apples is by color, and there's a distinct list of colors that are good for apples. So, we can have code like this:

[tt]
If Apple = Red or Apple = Green Or Apple = Yellow Then
Apple is good
Else
Apple is bad
[/tt]

But, we don't really care about whether the apple is good, we only care about the apple being bad, so we can rewrite this to:

[tt]
If [!]Not([/!]Apple = Red or Apple = Green Or Apple = Yellow[!])[/!] Then
Apple is bad
Else
Apple is good
[/tt]

More re-writing...


[tt]
If [!]Not([/!]Apple = Red or Apple = Green Or Apple = Yellow[!])[/!] Then
RaiseError "Apple is bad"
Get out of stored procedure
[/tt]

Without the parenthesis, the logic is different, and in my opinion, it becomes harder to follow. I like easily understood code.

-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

 

[gmmastros]

Just to clarify... the parenthesis are not strictly required, but without them you need to change the logic conditions. The 2 blocks of code produce the same results.

Declare @Apple VarChar(20)

Set @Apple = 'Yellow'

If [!]Not ([/!]@Apple = 'Red' [green]or[/green] @Apple = 'Green' [green]or[/green] @Apple = 'Yellow'[!])[/!]
  Print 'Apple is bad'
Else
  Print 'Apple Is Good'

Declare @Apple VarChar(20)

Set @Apple = 'Yellow'

If [!]Not[/!] @Apple = 'Red' [green]and[/green] [!]Not[/!] @Apple = 'Green' [green]and[/green] [!]Not[/!] @Apple = 'Yellow'
  Print 'Apple is bad'
Else
  Print 'Apple Is Good'

Notice that when you remove the parenthesis, you need to use NOT for each condition and change or's to and's. Personally, I think the first block of code is easier to understand. Please understand that this is my preference, and I'm not in any way saying "you gotta do things my way". It's a preference, that's all.

-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]

Well, preference or not I am in agreement. Much easier to know what is meant.

Thank you very much for taking the time to explain the logic flow with the parans.

What if..

We care if the file number entered is properly formatted, and the letter prefix matches an entry in a lookup table?

I know this is not working, but it may show what I am thinking.

ALTER PROCEDURE [dbo].[usp_TrackingInput_v1]
    -- Add the parameters for the stored procedure here
    @FileNumber VarChar(15) = Null,
    @BoxNumber VarChar(50) = Null,
    @TrackingDate DateTime = Null
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate the incoming data
    If Not (@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.')
      BEGIN
        RAISERROR(50500,16,1)
        RETURN
      END
[red]
	If (upper(LEFT(@FileNumber, 1)) Not In (Select FileNumPrefix from tblFileNumPrefix))
	Else If (upper(LEFT(@FileNumber, 3)) Not In (Select FileNumPrefix from tblFileNumPrefix))
	  BEGIN
        RAISERROR(50500,16,1)
        RETURN
      END
[/red]

    -- We passed validation, now insert the data.

    DECLARE @LogonName varchar(18) -- Logged on user interting the new record
    SELECT @LogonName = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),
           @TrackingDate = GetDate()
    
    Insert Into dbo.tblTrackingTable(EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)
VALUES(@LogonName,Host_Name(),@BoxNumber,@FileNumber,@TrackingDate)
End

Thanks

John Fuhrman

 

[gmmastros]

In this case, I would probably create another variable for the FileNumberPrefix and then perform an exists check. Something like this:

[tt]
Declare @FileNumberPrefix VarChar(15)
Set @FileNumberPrefix = Left(@FileNumber, PatIndex('%[^a-z.]%', @FileNumber + '1')-1)

If Not Exists(Select 1 From tblFileNumPrefix Where FileNumPrefix = @FileNumberPrefix)
BEGIN
RAISERROR(50500,16,1)
RETURN
END
[/tt]

There's some trickiness involved with getting the prefix, please make sure you completely understand.

-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]

OK, that work beautifuly.

I understand the logic in the if exists.

Except this...
(Select 1 From tblFileNumPrefix Where FileNumPrefix = @FileNumberPrefix)

but let me try.

Select 1st row that FileNumPrefix equals @FileNumberPrefix

I have seen the "Select 1 from Table where something = @something" before so I just wanted to be sure I am understanding what it is doing.

Thanks

John Fuhrman

 

[gmmastros]

Select 1st row that FileNumPrefix equals @FileNumberPrefix

Not quite.

What it's really saying is: Select the hard coded value 1 for each row from your table where FileNumPrefix equals @FileNumberPrefix

This is slightly better than selecting a column (for performance reasons).

When you combine this with the exists function, you end up with something that is extremely efficient, especially if you have an index on the filter condition.

-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]

Table for prefix lookups

row Column
1 a
2 b
3 c

Select [red]1[/red] From tblFileNumPrefix Where FileNumPrefix = @FileNumberPrefix

result
row Column
1 a


returns row 1 as the result?


Thanks

John Fuhrman

 

[sparkbyte]

Oh, I see, it return no column data when there is not a match.

Row nocolumnname
1 1


So the if not exists see the 1 returned when a match is found, otherwise it fails.



Thanks

John Fuhrman

 

[gmmastros]

No. Try running this query:

Select 1, 'Hard Coded Data', Row, Column 
From   tblFileNumPrefix

The important thing to realize here is that we are using this in an exists function. Basically, exists returns true there are any rows returned by the query in parenthesis. Exists is fast because it doesn't look at the data, and will return true as soon as it finds a row that satisfies the condition.

If we use a column name in the query, then there could be a row lookup or bookmark lookup (to get it's value). But exists doesn't care about the value, so we hard code the value. In fact, anything could be used.

If r937 (Rudy) was answering this question, he would have done this:

Declare @FileNumberPrefix VarChar(15)
Set @FileNumberPrefix = Left(@FileNumber, PatIndex('%[^a-z.]%', @FileNumber + '1')-1)

If Not Exists(Select [!]937[/!] From tblFileNumPrefix Where FileNumPrefix = @FileNumberPrefix)
      BEGIN
        RAISERROR(50500,16,1)
        RETURN
      END

Either way, you get the same results because exists doesn't care about the value, it only cares about the existence of a row. It doesn't even care how many rows.

-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]

lightbulb dawns on on marblehead...... and the light is blinding.

Here is the working USP.

Alter PROCEDURE [dbo].[usp_TrackingInput_v3]
    -- Add the parameters for the stored procedure here
    @FileNumber VarChar(15) = Null,
    @BoxNumber VarChar(50) = Null,
    @TrackingDate DateTime = Null

/******************************************************
 Custom error messages.
*******************************************************/
--sp_dropmessage @msgnum = 50500
--sp_addmessage @msgnum = 50500,
--	@severity = 16,
--  @msgtext = N'The File Number you have entered does not meet existing criteria and will not be Accepted.'
--
--sp_dropmessage @msgnum = 50501
--sp_addmessage @msgnum = 50501,
--	@severity = 16,
--  @msgtext = N'The File Number you have entered begin with a correct PREFIX and will not be Accepted.'

AS
BEGIN
    SET NOCOUNT ON;

    -- Validate the incoming data
    If Not (@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.')
      BEGIN
        RAISERROR(50500,16,1)
        RETURN
      END

Declare @FileNumberPrefix VarChar(15)
Set @FileNumberPrefix = Left(@FileNumber, PatIndex('%[^a-z.]%', @FileNumber + '1')-1)
If Not Exists(Select 1 From tblFileNumPrefix Where FileNumPrefix = @FileNumberPrefix)
      BEGIN
        RAISERROR(50501,16,1)
        RETURN
      END

    -- We passed validation, now insert the data.

    DECLARE @LogonName varchar(18) -- Logged on user interting the new record
    SELECT @LogonName = PARSENAME(REPLACE(SUSER_SNAME(), '\', '.'), 1),
           @TrackingDate = GetDate()
    
Insert Into dbo.tblTrackingTable(EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)
VALUES(@LogonName,Host_Name(),@BoxNumber,@FileNumber,@TrackingDate)
End

THANKS!!!!!!!!!

Just one other question about all this.
To use this to test file numbers rather than use it to test and insert. Would I change it to a function (SQL2005)?

As I am working with this, I just discovered that by doing this as an USP MS Access ADP form now does not show columns when the USP is selected as the datasource. Which means I would have to redo the form as an unbound form. The problem is that the current form scrolls the data as new enteries are added filter by the user ID and curent Day.

I will have to ponder how to use the knowledge gained by all this.

Maybe bound main form / unbound subform.
refresh main form on data entry from subform.

The other thaught was to Create a table-valued function that uses the filenumber testing which would allow me to keep the form bound.


Thanks

John Fuhrman